Re: determine sequence name for a serial

On Wed, Oct 27, 2004 at 09:06:15PM -0700, Robby Russell wrote:
>
> Ok, so how would I go about getting the sequence name for a SERIAL
> field on any given schema.table? I would like to build a function
> that would return this value if I pass it the schema and table (and
> fieldname is necessary)

PostgreSQL 8.0 (still in beta) has pg_get_serial_sequence():

test=> SELECT pg_get_serial_sequence('foo', 'id');
pg_get_serial_sequence
------------------------
public.foo_id_seq

Here's a query that you might find useful:

SELECT s1.nspname || '.' || t1.relname AS tablename,
a.attname,
s2.nspname || '.' || t2.relname AS sequencename
FROM pg_depend AS d
JOIN pg_class AS t1 ON t1.oid = d.refobjid
JOIN pg_class AS t2 ON t2.oid = d.objid
JOIN pg_namespace AS s1 ON s1.oid = t1.relnamespace
JOIN pg_namespace AS s2 ON s2.oid = t2.relnamespace
JOIN pg_attribute AS a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE t1.relkind = 'r'
AND t2.relkind = 'S';

I posted a somewhat different query in a recent thread about
automatically updating all sequences after importing data:

http://archives.postgresql.org/pgsql-general/2004-10/msg00673.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/