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

Re: determine sequence name for a serial

From: Robby Russell <robby(at)planetargon(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: determine sequence name for a serial
Date: 2004-10-28 17:42:19
Message-ID: 1098985339.7477.31.camel@vacant (view raw or flat)
Thread:
Lists: pgsql-general
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(at)planetargon(dot)com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 and PHP4 ---
****************************************/

In response to

Responses

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2004-10-28 17:44:00
Subject: Re: primary key and existing unique fields
Previous:From: Michael FuhrDate: 2004-10-28 17:35:57
Subject: Re: field incrementing in a PL/pgSQL trigger

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