CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
Baker
Baker

Reputation: 505

Dynamic variable names in plpgsql (String to variable name)

Is there a way to use variable name dynamically, I mean compose it as a string and then use it.

Please don't tell me to use array, the example I have shown is only simplification for better picture.

do $$ 
declare 
  var1 int:=1; 
  var2 int:=2; 
  var3 int:=3;  
  i int; 
begin
  raise notice 'Variable x %' , var1; 
  raise notice 'Variable x %' , var2; 
  raise notice 'Variable x %' , var3; 
  for i in 1..3 loop raise
     notice 'Variable x %' , 'var' || i; 
  end loop; 
end 
$$

Result

NOTICE:  Variable x 1
NOTICE:  Variable x 2
NOTICE:  Variable x 3
NOTICE:  Variable x var1
NOTICE:  Variable x var2
NOTICE:  Variable x var3

So I would need something like this except the result should be the numbers not the string Variable x var1 but Variable x 1

And Execute doesn't work either

do 
$$ 
declare 
  var1 text = 'car'; 
  var2 text = 'truck'; 
  var3 text = 'boat'; 
  code text ; 
begin 
  for i in 1..3 loop 
    code  = 'Insert into My_table values (' || i || ',' || 'var' || i || ');'; 
    execute   code;
  end loop; 
end 
$$

ERROR:  column "var1" does not exist
LINE 1: Insert into My_table values (1,var1);

Any ideas how to dynamically compose variable name, and as I said those examples are simple just to show my point. I need to change more than just a number in variable therefore array isn't a solution for me.

Upvotes: 5

Views: 3187

Answers (3)

Larrihoover
Larrihoover

Reputation: 76

You can do something like this but be careful if you have more than 16,000 variables to create:

$BODY$
declare

sql_text text;
_ptc int;

begin

sql_text = 'create table <schema>.<table> as 
    select id, process_date ' ;

for _ptc in select distinct(preferred_term_code) from <schema>.<table> order by 1

loop sql_text = sql_text || ', case when preferred_term_code = '||_ptc||' then 1 else 0 end as ptc_'||_ptc|| ' ';
raise notice '_ptc: %', _ptc;
end loop;
sql_text = sql_text || ' from <schema>.<table>
;';

execute sql_text;
grant select on <chema>.<table> to public;
raise notice 'Checks in the mail!';
end;
$BODY$

Upvotes: 4

&#246;z&#252;m
&#246;z&#252;m

Reputation: 1363

There seems no way in pl/pgSQL, but you may install pl/v8 procedural language for PostgreSQL and use JavaScript's eval feature.

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45795

PLpgSQL doesn't support any way, how to dynamically evaluate variables. I am sorry. Nothing like eval is there.

Upvotes: 0

Related Questions