determine sequence name for a serial

#1

I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.

For example,

CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);

\d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('public.foo_id_seq'::text)
bar | text |
Indexes:
"foo_pkey" primary key, btree (id)

Now, I have figured out how to get a list of all the sequences with:

foo=> SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
relname
------------
foo_id_seq
(1 row)

I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and pg_class in order to find the sequence for a specific field in public.foo.

Can anyone point me in the right direction? I am trying to get out of the habit of hard-coding the sequence names in my code.

Now that I think of it, I am lacking 'public.' as well from my query.

Ok, so how would I go about getting the sequence name for a SERIAL field onany given schema.table? I would like to build a function that would returnthis value if I pass it the schema and table (and fieldname is necessary)

Thanks,

Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHA30QaQZBaqXgwRAkcMAJ4vTrjsPBA+UWsbOR/sy4KHofIOEgCeNS3X
WQk4+eh0LUgeeIosTGKISVM=
=3fZd
-----END PGP SIGNATURE-----

#2

On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:

Quote:

I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.

For example,

CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);

\d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('public.foo_id_seq'::text)
bar | text |
Indexes:
"foo_pkey" primary key, btree (id)

Now, I have figured out how to get a list of all the sequences with:

foo=> SELECT relname FROM pg_class WHERE relkind='S' AND relname !~ '^pg_';
relname
------------
foo_id_seq
(1 row)

I can find public.foo in pg_tables, but I am not sure how to relate pg_tables and pg_class in order to find the sequence for a specific field in public.foo.

Can anyone point me in the right direction? I am trying to get out of thehabit of hard-coding the sequence names in my code.

Now that I think of it, I am lacking 'public.' as well from my query.

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)

Thanks,

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

So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.

Help. :-)

Thanks again,

-Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHaR0QaQZBaqXgwRAn1hAJ9ZnPngtDrOw1kP/fbklTXhY/j5QACeMA2w
pZlgGypzY9L53C3LU4lrwm0=
=cpsO
-----END PGP SIGNATURE-----

#3

On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:

Quote:

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

So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.

Also, I am trying to avoid assuming that the sequence name will be:
schema.table_id_seq

The goal is to determine the sequence name for any schema.table that has
a SERIAL sequence (because you can create a sequence with a different
name) ... and if the column name isn't 'id'

for example:
foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
pg_class WHERE relname = 'bar');
adsrc
-------------------------------------
nextval('public.bar_nid_seq'::text)
(1 row)

The schema.table_id_seq wouldn't work under this scenario.

any thoughts or pointers?

Thanks,

Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgHvC0QaQZBaqXgwRAks/AJ9HBpA7l/N0z3x/nBOcyPFX0/BdJgCghPqL
lC5+82mJUmyUclppDcyedD8=
=EikY
-----END PGP SIGNATURE-----

#4

On Wed, Oct 27, 2004 at 09:33:21PM -0700, Robby Russell wrote:

Quote:

So, it was a nice attempt, but I am back to the need to of determining
the sequence name using a schema and a table.

The schema of a table is stored in pg_class.relnamespace, which is an
Oid of the pg_namespace catalog. With that and your previous query you
should be set.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La felicidad no es mañana. La felicidad es ahora"

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#5

# 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#6

Robby Russell <robby (AT) planetargon (DOT) com> writes:

Quote:

Ok, so how would I go about getting the sequence name for a SERIAL
field on any given schema.table?

8.0 will have a function pg_get_serial_sequence to do this for you.
If you can't wait, the secret is to look in pg_depend for the dependency
link from the serial sequence to its column.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#7

On Wed, Oct 27, 2004 at 09:06:15PM -0700, Robby Russell wrote:

Quote:

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...0/msg00673.php

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#8

# 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#9

On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote:

Quote:

# CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNStext 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;

Thanks, this seems to work well. My goal is to actually create a php
function that takes a result and returns the insert_id like
mysql_insert_id() does, but without needing to know the sequence names
and such. I would make a psql function, but I don't always have that
option with some clients existing systems.

-Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | robby (AT) planetargon (DOT) com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBBgS970QaQZBaqXgwRAmwIAJ9xGhi8cQ3p2oTA9Ih+Xo dcnCHajgCgktnT
eT0GO3tjolD2k7K2jRbDOX8=
=3Nd8
-----END PGP SIGNATURE-----

#10

On Thursday October 28 2004 11:42, Robby Russell wrote:

Quote:

Thanks, this seems to work well. My goal is to actually create a php
function that takes a result and returns the insert_id like
mysql_insert_id() does, but without needing to know the sequence names
and such. I would make a psql function, but I don't always have that
option with some clients existing systems.

An alternative is to simply select nextval() from a separately-created
sequence object to get the serial value, then insert with that value. No
need to have a serial column then, but you do need to explicitly create the
sequence object, as opposed to SERIAL.

But I didn't understand why you care to get rid of the explicit reference to
the sequence object in your code in the first place. In PostgreSQL, at
least for the past 5 years if not longer, if you create a SERIAL column for
(schemaname, tablename, columnname), then your sequence will *always* be
"schemaname.tablename_columnname_seq". If that naming convention changes,
there will be a whole lotta breakage world-wide.

Ed

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly