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 | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now supporting PHP5 and PHP4 --- ****************************************/
signature.asc
Description: This is a digitally signed message part