On Thursday 10 January 2008 08:53:14 am Adrian Klaver wrote:
> On Wednesday 09 January 2008 9:34 pm, johnf wrote:
> > On Wednesday 09 January 2008 09:15:00 pm Adrian Klaver wrote:
> > > The key seems to be using the pg_attrdef table. It holds the default
> > > values for table columns. Use the oid of the table to find
> > > pg_attrdef.adrelid and the pg_attribute.attnum to find
> > > pg_attrdef.adnum. pg_attrdef.adbin contains a binary representation of
> > > the default value for the column. This can be made readable using
> > > system function pg_get_expr(expr_text, relation_oid) where relation_oid
> > > is the table oid. I haven't worked out a complete solution yet as it
> > > involves parsing out the sequence name.
> > >
> > > Hope this helps,
> >
> > Exactly. I want the select to return the sequence name.
>
> <snip>
>
> > But only give the constraint name and not the sequence name associated
> > with the constraint name.
>
> Here is what I am trying:
>
> SELECT pg_get_expr(adbin,17337) from pg_attrdef where adrelid =17337 and
> adnum=1;
> pg_get_expr
> ---------------------------------------------------
> nextval(('plant1_p_item_no_seq'::text)::regclass)
>
> The value passed to adrelid is the oid of the table (plant1) and the value
> passed to adnum is the column number (p_item_no). adbin is a binary
> representation of the default value for the field. There is also adsrc
> which is a text version of the default value. The docs for 8.2 say adsrc is
> present for historical reasons and adbin is a more stable field to work
> with. The system function pg_get_expr(expr_text,relation_oid) reverse
> compiles adbin into text form. pg_get_expr is available back to 7.4. Since
> End of Life was just declared on 7.3 I don't see that as being to big a
> problem. Obviously this is a cut down version of the query needed to
> extract the necessary information. Then there is the need to parse out the
> sequence name.
Base on your first info try this:
SELECT substring((SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_attrdef d
WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum
AND a.atthasdef
) FROM E'nextval[^\']*\'([^\']*)')
FROM pg_attribute a
LEFT JOIN pg_class c ON c.oid = a.attrelid
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum =
a.attnum AND a.atthasdef
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE (c.relname = 'mytest') AND a.attname = 'pkid'
AND NOT a.attisdropped
AND a.attnum > 0
AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%'
--
John Fabiani
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]
