Dynamic variable names in plpgsql (String to variable name)

Question

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.

The closest thing in postgres for eval-like functionality, is EXECUTE, but No substitution of PL/pgSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters -- so no, it cannot be done the way, you requested. In fact, using arrays won't help you either (usually, but maybe something like a hash-table / map might help you (like hstore or json(b)) — pozs
– pozs, Commented Aug 17, 2015 at 13:48
wildplasser: pr is var, it is just shortcut for variable but in my language and I translated it AFTER copying and I forgot to rewrite this one, so no impact on code ... but thx I have edited it — Baker
– Baker, Commented Aug 18, 2015 at 8:05

Larrihoover · Accepted Answer · 2016-03-02 15:16:40Z

4

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$

answered Mar 2, 2016 at 15:16

Larrihoover

766 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pavel Stehule · Accepted Answer · 2015-08-18 10:50:25Z

0

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

answered Aug 18, 2015 at 10:50

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Comments

özüm · Accepted Answer · 2015-12-18 12:21:35Z

0

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

edited Dec 18, 2015 at 12:21

answered Dec 18, 2015 at 7:21

özüm

1,40314 silver badges25 bronze badges

Collectives™ on Stack Overflow

Dynamic variable names in plpgsql (String to variable name)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related