On Thursday 10 January 2008 01:14:38 pm Adrian Klaver wrote:
> -------------- 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]
Oh I did not realize the I lost compatibility - that's not good. At the
moment we still support 7.4. That's because 7.4 is still very popular.
BTW I forgot schema checks - so here is the new select:
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 n.nspname='public'
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]
