Skip site navigation (1) Skip section navigation (2)

Re: determine sequence name for a serial

From: Jonathan Daugherty <cygnus(at)cprogrammer(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: determine sequence name for a serial
Date: 2004-10-28 05:45:48
Message-ID: 20041028054548.GA19919@vulcan.cprogrammer.org (view raw or flat)
Thread:
Lists: pgsql-general
# 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;

As per Tom's mention of pg_depend, here's something that seems to do
the trick for the time being, assuming the column is a serial:

-- get_sequence(schema_name, table_name, column_name)

CREATE OR REPLACE FUNCTION get_sequence (text, text, text) RETURNS
text AS '
  SELECT seq.relname::text
  FROM pg_class src, pg_class seq, pg_namespace, pg_attribute,
pg_depend
  WHERE
    pg_depend.refobjsubid = pg_attribute.attnum AND
    pg_depend.refobjid = src.oid AND
    seq.oid = pg_depend.objid AND
    src.relnamespace = pg_namespace.oid AND
    pg_attribute.attrelid = src.oid AND
    pg_namespace.nspname = $1 AND
    src.relname = $2 AND
    pg_attribute.attname = $3;
' language sql;

-- 
  Jonathan Daugherty
  http://www.cprogrammer.org

In response to

Responses

pgsql-general by date

Next:From: Sim ZacksDate: 2004-10-28 06:39:30
Subject: Re: Bug: 8.0 beta1 either view optimization or pgdump/pgrestore
Previous:From: Michael FuhrDate: 2004-10-28 05:33:30
Subject: Re: determine sequence name for a serial

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group