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!
