Thread: determine sequence name for a serial

determine sequence name for a serial

From

Robby Russell

Date:

28 October 2004, 08:06:38

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
sequencefor 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 on any given schema.table? I would like to
builda function that would return this 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@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

Attachment

signature.asc

Re: determine sequence name for a serial

From

Robby Russell

Date:

28 October 2004, 08:33:44

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
sequencefor 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
mycode.  
>
> 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
builda 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@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

Attachment

signature.asc

Re: determine sequence name for a serial

From

Robby Russell

Date:

28 October 2004, 08:55:54

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  | robby@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

Attachment

signature.asc

Re: determine sequence name for a serial

From

Alvaro Herrera

Date:

28 October 2004, 09:14:02

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"

Re: determine sequence name for a serial

From

Tom Lane

Date:

28 October 2004, 09:15:08

Robby Russell <robby@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

Re: determine sequence name for a serial

From

Jonathan Daugherty

Date:

28 October 2004, 09:15:24

# 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

Re: determine sequence name for a serial

From

Michael Fuhr

Date:

28 October 2004, 09:33:43

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-general/2004-10/msg00673.php

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

Re: determine sequence name for a serial

From

Jonathan Daugherty

Date:

28 October 2004, 09:46:58

# 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

Re: determine sequence name for a serial

From

Robby Russell

Date:

28 October 2004, 21:42:43

On Wed, 2004-10-27 at 22:45 -0700, Jonathan Daugherty wrote:
> # 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;
>

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@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

Attachment

signature.asc

Re: determine sequence name for a serial

From

"Ed L."

Date:

29 October 2004, 02:51:54

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

Re: determine sequence name for a serial

From

Robby Russell

Date:

29 October 2004, 03:08:05

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  | robby@planetargon.com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
*    --- Now supporting PHP5 ---
****************************************/

Attachment

signature.asc

Re: determine sequence name for a serial

From

Jonathan Daugherty

Date:

29 October 2004, 03:26:22

# 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, (503) 667-4564

Re: determine sequence name for a serial

From

Michael Fuhr

Date:

29 October 2004, 03:32:04

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/

Re: determine sequence name for a serial

From

"Ed L."

Date:

29 October 2004, 03:50:53

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

Re: determine sequence name for a serial

From

Greg Stark

Date:

29 October 2004, 03:56:01

"Ed L." <pgsql@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

Re: determine sequence name for a serial

From

Alvaro Herrera

Date:

29 October 2004, 05:17:03

On Thu, Oct 28, 2004 at 07:55:51PM -0400, Greg Stark wrote:
>
> "Ed L." <pgsql@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