Re: Best way to create a sequence generator at run time?

Well I guess I'll be answering all my own questions today ;)

And the correct answer turns out to be....

execute 'select nextval(' || quote_literal(self.cnum_generator) || ')' into my_cnum;
return my_cnum;

Was hoping to avoid the temporary variable (my_cnum), but hey, it works!

And I'm off to the 9.0 release party in SF, see everyone there even though I won't know who anyone is!

- Leon

> I'm converging on a good solution, but have just one (hopefully) problem left.
> I need to get the nextval for a sequence whose name has been stored, and is thus
> not available prior to runtime. I am trying to use EXECUTE, but can't seem to get
> it right. Suggestions?
>
> Here's the function extract in question:
>
> create or replace function ...
> ) returns bigint as
> $$
> declare
> self subsystem%rowtype; -- has an attribute where the sequence name is stored
> begin
> select * from subsystem into strict self where (name = p_subsystem and domain = p_domain);
>
> -- self.cnum_generator is a text value holding the name of the previously created sequence
> -- I've tested to ensure that it is holding the correct value, so no worries there, it is a valid sequence
>
> -- Now here is the trouble - none of these statements seem to work or parse correctly:
>
> return execute 'nextval(' || self.cnum_generator || ')'; -- NOPE
>
> new_num := nextval(self.cnum_generator); -- I didn't expect this one to work, but might as well try, NOPE
>
> return query execute 'nextval( $1 )' using self.cnum_generator; -- no good either
>
> return query execute 'nextval(' || self.cnum_generator || ')'; -- NOPE
>
> -- accck! Phhht! Help!