-------------- Original message ----------------------
From: johnf <[EMAIL PROTECTED]>
> 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
>
Works here. The only comment is that use of E' limits it to 8.1+.
Thanks for working on this.
--
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]
