Hi,

Is there demand for this syntax:

ALTER SEQUENCE ON table(col) CYCLE 100;

It would allow us to become sequence-name independent...

Chris



Mon, 24 Nov 2003 14:07:23 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
ALTER SEQUENCE enchancement
On Mon, 24 Nov 2003, Christopher Kings-Lynne wrote:

> Is there demand for this syntax:
> 
> ALTER SEQUENCE ON table(col) CYCLE 100;
> 
> It would allow us to become sequence-name independent...

The above is an operation that would not help me a lot, but a way of 
performing currval() without knowing the sequence name would be good.

-- 
/Dennis


Mon, 24 Nov 2003 07:11:59 +0100 (CET)
Dennis Bjorklund <db@zigo.dhs.org>
Re: ALTER SEQUENCE enchancement
>>Is there demand for this syntax:
>>
>>ALTER SEQUENCE ON table(col) CYCLE 100;
>>
>>It would allow us to become sequence-name independent...
> 
> 
> The above is an operation that would not help me a lot, but a way of 
> performing currval() without knowing the sequence name would be good.

It will help in cases such as the 7.3-7.4 upgrade where a few of my 
sequence names will get renamed because they maxed out at 32 characters...

I'll see about versions of currval() and nextval() that are sequence 
name independent as well...

Chris



Mon, 24 Nov 2003 14:20:41 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ALTER SEQUENCE enchancement
On Mon, 2003-11-24 at 01:07, Christopher Kings-Lynne wrote:
> Hi,
> 
> Is there demand for this syntax:
> 
> ALTER SEQUENCE ON table(col) CYCLE 100;
> 
> It would allow us to become sequence-name independent...

I think the right approach to this problem would be to implement
IDENTITIES and GENERATORS per 2k3 spec -- almost what you show here.

Check DB2 docs for examples.


Mon, 24 Nov 2003 08:29:30 -0500
Rod Taylor <pg@rbt.ca>
Re: ALTER SEQUENCE enchancement
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Is there demand for this syntax:
>
> ALTER SEQUENCE ON table(col) CYCLE 100;

What if the values in a column are generated via a sequence that was
created independently -- i.e. it's not a SERIAL column?

I'm not very enthusiastic about features that assume a one-to-one
mapping between sequences and columns; in general, the mapping is
many-to-many.

-Neil


Mon, 24 Nov 2003 11:10:28 -0500
Neil Conway <neilc@samurai.com>
Re: ALTER SEQUENCE enchancement

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
> The above is an operation that would not help me a lot, but a way of
> performing currval() without knowing the sequence name would be good.
 
You could do this with a function. Here is a quick one in SQL:
 
CREATE OR REPLACE FUNCTION seqname(NAME,NAME) RETURNS TEXT AS '
SELECT TRIM(BOTH \'\'\'\' FROM SUBSTRING(d.adsrc, \'\'\'.*\'\'\'))
FROM pg_catalog.pg_attrdef d, pg_catalog.pg_attribute a, pg_class c
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
AND c.relname = $1
AND c.oid = a.attrelid
AND a.attname = $2;
' LANGUAGE SQL;
 
Usage: SELECT nextval(seqname('tablename','colname'));
 
You might also want to simply keep a table of the sequence names
if you plan on doing this a lot, or make sure you name them in
a consistent and unsurprising manner.
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200312022159
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQE/zVV/vJuQZxSWSsgRArvaAKCM91V5su/AoOKXWVf+JhdrklRfQQCfXXfp
R7Pqjd/U0p+xSrS+sMxEoRE=
=FA6a
-----END PGP SIGNATURE-----




Wed,  3 Dec 2003 03:17:09 -0000
greg@turnstep.com
Re: ALTER SEQUENCE enchancement