Thread: PL/PGSQL - dynamic variable names
Hi,
I am trying to EXECUTE .. INTO a variable that I want to be dynamically named.
stuff := '{a,b,c,d}';
FOR i IN 1..4 LOOP
thing := stuff[i];
-- stuff_a, stuff_b etc are functions - substitution works here
exec_string := 'SELECT stuff_' || thing || '(''' || arg1 || ''',''' || arg2 || ''');';
-- thing not substituted
-- value of thing above is replaced on each iteration rather than assigning result into variables a,b,c,d
EXECUTE exec_string INTO thing;
END LOOP;
The variable names always seems to be interpreted literally though. Is there a way to make this work in PL/PGSQL?
Of course it would be nice if EXECUTE supported 'SELECT INTO' :)
cheers,
Ben
I am trying to EXECUTE .. INTO a variable that I want to be dynamically named.
stuff := '{a,b,c,d}';
FOR i IN 1..4 LOOP
thing := stuff[i];
-- stuff_a, stuff_b etc are functions - substitution works here
exec_string := 'SELECT stuff_' || thing || '(''' || arg1 || ''',''' || arg2 || ''');';
-- thing not substituted
-- value of thing above is replaced on each iteration rather than assigning result into variables a,b,c,d
EXECUTE exec_string INTO thing;
END LOOP;
The variable names always seems to be interpreted literally though. Is there a way to make this work in PL/PGSQL?
Of course it would be nice if EXECUTE supported 'SELECT INTO' :)
cheers,
Ben
On 06/28/2011 06:29 PM, Ben Carbery wrote: > Hi, > > I am trying to EXECUTE .. INTO a variable that I want to be > dynamically named. > > stuff := '{a,b,c,d}'; > > FOR i IN 1..4 LOOP > thing := stuff[i]; > > -- stuff_a, stuff_b etc are functions - substitution works here > exec_string := 'SELECT stuff_' || thing || '(''' || arg1 || > ''',''' || arg2 || ''');'; > > -- thing not substituted > -- value of thing above is replaced on each iteration rather > than assigning result into variables a,b,c,d > EXECUTE exec_string INTO thing; > > END LOOP; > > The variable names always seems to be interpreted literally though. Is > there a way to make this work in PL/PGSQL? > > Of course it would be nice if EXECUTE supported 'SELECT INTO' :) > > cheers, > > Ben Hm, "a" isn't a variable,it's the value of stuff[1]. Did you try declaring a,b,c,d as the return type of the respective functions?
Hm, "a" isn't a variable,it's the value of stuff[1].
It's both..
DECLARE
a integer;
b integer;
The point is I want a dynamically named variable. Here I've named them the same as stuff[i] but they can be anything provided it is a different variable name on each loop iteration. The manual is not entirely clear on where loop variable substitution does and does not apply with FOR.
Did you try
declaring a,b,c,d as the return type of the respective functions?
How would this help?
Actually there is a section in the manual on this problem:
http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
On 29 June 2011 11:41, Ben Carbery <ben.carbery@gmail.com> wrote:
Hm, "a" isn't a variable,it's the value of stuff[1].
It's both..
DECLARE
a integer;
b integer;
The point is I want a dynamically named variable. Here I've named them the same as stuff[i] but they can be anything provided it is a different variable name on each loop iteration. The manual is not entirely clear on where loop variable substitution does and does not apply with FOR.
Did you try
declaring a,b,c,d as the return type of the respective functions?
How would this help?
Ben Carbery wrote: > > Hm, "a" isn't a variable,it's the value of stuff[1]. > > > It's both.. > > DECLARE > a integer; > b integer; > > The point is I want a dynamically named variable. Here I've named them > the same as stuff[i] but they can be anything provided it is a > different variable name on each loop iteration. The manual is not > entirely clear on where loop variable substitution does and does not > apply with FOR. > > > > Did you try > declaring a,b,c,d as the return type of the respective functions? > > > How would this help? I understand that your original post was pseudo-code, but aren't you forced into firing a fixed set of functions {stuff[]} which all use the same parameters? Seems that set of functions could be rolled into one which returns a list of values. Or are you also passing in a,b,c,d (with name-of-function values (strings)) and hoping to slam the function's return value (int?) back into the corresponding (perhaps out) parameter? Hm, you could pass in an array of names, iterate over the array, call the named function and load the return value into a single declared array, by index, in each iteration of the loop. Weird!