From: | Leon Starr <leon_starr(at)modelint(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Best way to create a sequence generator at run time? |
Date: | 2010-09-22 20:19:57 |
Message-ID: | 3452B1FA-924B-4B90-8D90-1A53BF0607DB@modelint.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Leon Starr | 2010-09-22 21:04:51 | Re: Best way to create a sequence generator at run time? |
Previous Message | Tom Lane | 2010-09-22 20:09:57 | Re: pg_dump |