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.
--
Adrian Klaver
[EMAIL PROTECTED]
_______________________________________________
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]
