Re: The need to know if a field is using/connected to a sequence - Mailing list pgsql-sql

Hi,

I found the answer in pgAdminII (I should have known this...),

I currently do it in a two step process (I need to know info about
sq_logile):

1) first get the oid of the table I like to know something about:

SELECT oid, relname, pg_get_userbyid(relowner) as tableowner, relacl,
relhasoids FROM pg_class WHERE ((relkind = 'r') OR (relkind = 's')) and
relname = 'sq_logfile' ORDER BY relname

2) Now fill in the number 157554 and get the table result, I got the 157554
iod from the above SQL.

SELECT 0::oid AS oid, a.attname, a.attnum, CASE WHEN (t.typlen = -1 AND
t.typelem != 0) THEN (SELECT at.typname FROM pg_type at WHERE at.oid =
t.typelem) || '[]' ELSE t.typname END AS typname, CASE WHEN ((a.attlen = -1)
AND ((a.atttypmod)::int4 = (-1)::int4)) THEN (0)::int4 ELSE CASE WHEN
a.attlen = -1 THEN CASE WHEN ((t.typname = 'bpchar') OR (t.typname = 'char')
OR (t.typname = 'varchar')) THEN (a.atttypmod -4)::int4 ELSE
(a.atttypmod)::int4 END ELSE (a.attlen)::int4 END END AS length,
a.attnotnull, (SELECT adsrc FROM pg_attrdef d WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum) AS default, (SELECT indisprimary FROM pg_index i,
pg_class ic, pg_attribute ia  WHERE i.indrelid = a.attrelid AND i.indexrelid
= ic.oid AND ic.oid = ia.attrelid AND ia.attname = a.attname  AND
indisprimary IS NOT NULL ORDER BY indisprimary DESC LIMIT 1) AS primarykey,
a.attstattarget FROM pg_attribute a, pg_type t WHERE a.atttypid = t.oid AND
attrelid = 157554::oid ORDER BY attnum

Hope this will help others aswell..

Ries

> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens A.Bhuvaneswaran
> Verzonden: donderdag 10 april 2003 10:26
> Aan: Ries van Twisk
> CC: pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] The need to know if a field is
> using/connected to a
> sequence
>
>
> > I currently use this to get field information of a table:
> > Now I just want to know (boolean field maby??) if a field
> is using/connected
> > to q sequence or not.
> > Which table should I access to get this information
>
> How can a boolean field use/connect q sequence? I got that you want to
> know the fields which use sequence for their default value.
> If i am right,
> here is the solution. The default value details are in
> pg_attrdef table.
>
> SELECT
> a.attnum,
> c.relname,
> a.attname,
> d.adsrc as default
> from
> pg_attribute a,
> pg_class c,
> pg_attrdef d
> where
> a.attrelid = c.oid
> and a.attnum = d.adnum
> and d.adrelid = c.oid
> and a.attnum > 0
> and c.relname = 'your_table_name'
> and d.adsrc ~* 'your_sequence_name'
> order by a.attnum;
>
> Hope it helps.
>
> regards,
> bhuvaneswaran
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>