determine sequence name for a serial

[Robby Russell]

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 | ro***@planetargon.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-----

[Robby Russell]

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

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 | ro***@planetargon.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-----

[Robby Russell]

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

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 | ro***@planetargon.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-----

[Alvaro Herrera]

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

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

[Tom Lane]

Robby Russell <ro***@planetargon.com> writes:

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 ma*******@postgresql.org

[Jonathan Daugherty]

# 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, [phone]

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

[Michael Fuhr]

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

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

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

[Jonathan Daugherty]

# 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 ma*******@postgresql.org

[Robby Russell]

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

# 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 | ro***@planetargon.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-----

[Ed L.]

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

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Robby Russell]

On Thu, 2004-10-28 at 16:51 -0600, Ed L. wrote:

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

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.

nextval, currval, either way, I would need to know the specific sequence
name. Was looking for a good way to pass a function a schema and table
and return a sequence. I got exactly what I was looking for and have
been able to build a function that will handle this for me. It's part of
a db layer class that I use with mysql and pgsql, and was using
mysql_insert_id and wanted to model a function that would return an id
like the mysql_insert_id function does. (one of the few pgsql/php
functions that doesn't exist in php natively..but does with mysql)

-Robby

--
/***************************************
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | ro***@planetargon.com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 ---
****************************************/

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

iD8DBQBBgXu40QaQZBaqXgwRAqN3AKCuxq/ZUnBv43W8fLEyuqoxQzWT/QCeLQ3f
CPtaQVng6szrZuNEcgalxls=
=Y+YN
-----END PGP SIGNATURE-----

[Jonathan Daugherty]

# 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.

When a table is renamed, related sequences' names don't change (as of
7.4.5). The ability to automagically pull the sequence based on the
schema.table.column would be nice if you don't want to worry about
having to update your table name and sequence name references in code.

--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, [phone]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Michael Fuhr]

On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote:

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".

Only for certain values of "always." Tables and columns can be renamed,
so the sequence name might no longer be "tablename_columnname_seq",
but rather "oldtablename_oldcolumnname_seq".

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

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

[Ed L.]

On Thursday October 28 2004 5:31, Michael Fuhr wrote:

On Thu, Oct 28, 2004 at 04:51:05PM -0600, Ed L. wrote:

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".

Only for certain values of "always." Tables and columns can be renamed,
so the sequence name might no longer be "tablename_columnname_seq",
but rather "oldtablename_oldcolumnname_seq".

Your point is well taken, I see the gotcha there, and thus the value of a
function.

Ed
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Greg Stark]

"Ed L." <pg***@bluepolka.net> writes:

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.

I hope you're wrong about people expecting that to be true because it isn't.
The resulting sequence name is limited to 63 characters and gets truncated if
it goes over. (63!? was it intended to be 64?) I believe the limit used to be
32 characters too.

In any case it's just plain good design to avoid unnecessary
interrelationships between different parts of the code. Practically speaking
it makes renaming something not involve an error-prone search and replace.
More importantly it makes it easier to verify that a piece of code is correct
without having to hunt down all the related bits to be sure the relationships
are correct. It also makes it possible to reuse or refactor the code.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Alvaro Herrera]

On Thu, Oct 28, 2004 at 07:55:51PM -0400, Greg Stark wrote:

"Ed L." <pg***@bluepolka.net> writes:

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.

I hope you're wrong about people expecting that to be true because it isn't.
The resulting sequence name is limited to 63 characters and gets truncated if
it goes over. (63!? was it intended to be 64?) I believe the limit used to be
32 characters too.

The NAMEDATALEN constant is defined to be 64, and that includes the
trailing \0, so identifiers are limited to 63 bytes.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Before you were born your parents weren't as boring as they are now. They
got that way paying your bills, cleaning up your room and listening to you
tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend