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:14:06
Message-ID: 20041028051406.GA19340@vulcan.cprogrammer.org (view raw or flat)
Thread:
Lists: pgsql-general
# 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

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2004-10-28 05:15:04
Subject: Re: determine sequence name for a serial
Previous:From: Alvaro HerreraDate: 2004-10-28 05:13:47
Subject: Re: determine sequence name for a serial

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