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
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
[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
