Re: determine sequence name for a serial

# I figured out how to get this:
#
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
#                adsrc
# ------------------------------------
#  nextval('public.foo_id_seq'::text)
# (1 row)
#
# However, this will break as soon as I do this:
#
# foo=> CREATE SCHEMA x;
# CREATE SCHEMA
# foo=> CREATE TABLE x.foo (id SERIAL PRIMARY KEY NOT NULL, x TEXT);
# NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for
# "serial" column "foo.id"
# NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
# "foo_pkey" for table "foo"
# CREATE TABLE
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
# ERROR:  more than one row returned by a subquery used as an
# expression

This should suffice to get you a string you can regex.  Other than the
default value setting for the serial, I don't see another link that
binds the serial to its sequence.

CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS '
  SELECT adsrc
  FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
  WHERE
    adrelid = pg_class.oid AND
    pg_class.relnamespace = pg_namespace.oid AND
    pg_attribute.attnum = pg_attrdef.adnum AND
    pg_attribute.attrelid = pg_class.oid AND
    pg_namespace.nspname = $1 AND
    pg_class.relname = $2 AND
    pg_attribute.attname = $3;
' language sql;

--
  Jonathan Daugherty
  Command Prompt, Inc. - http://www.commandprompt.com/
  PostgreSQL Replication & Support Services, (503) 667-4564