[GENERAL] Getting the column to a which a sequence belongs.
Hi, I'm trying to extend the Postgres support in my SQL tool. I'm trying to recreate the SQL for a sequence, and I wonder if there is a way to find out the column to which a sequence belongs. I'm talking either about sequences that are created automatically by PG when using the serial datatype or sequences that have been changed using ALTER SEQUENCE ... OWNED BY ... I know I can get the sequence that belongs to a column using pg_get_serial_sequence() (although only in 8.4), but I'm looking for the other way: given a sequence find out if it's owned by a column. So far debugging psql using the -E option didn't show up anything and I couldn't find any hints in the system catalogs documentation. Is this possible at all? As neither psql nor pgAdmin display this information, I suspect it's not. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the column to a which a sequence belongs.
On Thu, Aug 27, 2009 at 09:18:57PM +0200, Thomas Kellerer wrote: I'm trying to extend the Postgres support in my SQL tool. I'm trying to recreate the SQL for a sequence, and I wonder if there is a way to find out the column to which a sequence belongs. The information is all in the system catalogs; I've not had much opportunity to fiddle with them so far but the following may be a start to help get things out for you. SELECT c.relname, a.attname, t.relname FROM pg_class c, pg_depend d, pg_class t, pg_attribute a WHERE c.relkind = 'S' AND d.objid = c.oid AND d.refobjid= t.oid AND (d.refobjid,d.refobjsubid) = (a.attrelid,a.attnum); The first reference to pg_class can probably be dropped as you can convert the names of tables/sequences into their oid by using literals of type regclass. For example, to pull out all the column names from table foo, you can do: SELECT attname FROM pg_attribute WHERE attrelid = 'foo'::regclass; Have a look here for docs: http://www.postgresql.org/docs/current/static/catalogs.html -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting the column to a which a sequence belongs.
Sam Mason wrote on 27.08.2009 21:51: The information is all in the system catalogs; I've not had much opportunity to fiddle with them so far but the following may be a start to help get things out for you. SELECT c.relname, a.attname, t.relname FROM pg_class c, pg_depend d, pg_class t, pg_attribute a WHERE c.relkind = 'S' AND d.objid = c.oid AND d.refobjid= t.oid AND (d.refobjid,d.refobjsubid) = (a.attrelid,a.attnum); Ah great, I didn't realize I could use pg_depend for this. Thanks, works like a charm! This is exactly what I was looking for. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
