On Thursday 10 January 2008 4:49 pm, johnf wrote:
> On Thursday 10 January 2008 04:31:05 pm Adrian Klaver wrote:
> > On Thursday 10 January 2008 4:08 pm, johnf wrote:
> > > On Thursday 10 January 2008 01:14:38 pm Adrian Klaver wrote:
> > > > 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%'
> >
> > You can use the old escape scheme with the new versions. The only problem
> > is that it emits warnings by default. You can stop the warnings by
> > setting escape_string_warning =off in postgresql.conf . Something else to
> > keep in mind, the double cast
> > (nextval(('plant1_p_item_no_seq'::text)::regclass)) for the sequence name
> > is 8.1+. Prior to that is was-
> > nextval('plant1_p_item_no_seq'::text).
>
> I no longer have access to a 7.4 machine. So I didn't realize I was not
> casting correctly. Can you try making the changes and see if still works?
> There must be someway to parse correctly.
>
> thanks
The new version works on my copy of 8.2. It is not so much a casting issue as
a regex one. I'm afraid my regex skills are poor so I can't tell if the
expression you are using can cope with the difference between
nextval((...)..) and nextval(...) when extracting the sequence name. I don't
have instance of 7.4 either. I have to go to a meeting tonight, but tomorrow
or this weekend I will set up a 7.4 cluster and test the above query.
Thanks,
--
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]
