Обсуждение: ALTER SEQUENCE enchancement

Hi,

Is there demand for this syntax:

ALTER SEQUENCE ON table(col) CYCLE 100;

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

Chris

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

>>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

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.

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

-----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-----