using variable as part of the statement

Question

I want to use a variable as part of the statement, but it says that "tableref" doesn't exist.

CREATE OR REPLACE FUNCTION ff(tipo_acta integer, hasta date)
  RETURNS void AS
$BODY$
DECLARE 
 tableref varchar;
 r record;
BEGIN
 if tipo_acta = 1 then
          tableref = 't1';
 elsif tipo_acta = 2 then tableref = 't2';
 else tableref = 't3';
end if;

for r select id from tableref where somedate >= hasta loop
--
end loop;

I tried to use EXECUTE 'select id from ' || tableref || ' where....' but doesn't work either

I thought to get the record first with select id into r from t1 where .. and then use it in the loop, but there seems to be no way to use a record in a loop like that:

FOR r LOOP
....
END LOOP;

Pablo Santa Cruz · Accepted Answer · 2018-06-01 13:13:06Z

1

You need to use dynamic sql for that. You need to use execute command to do that in PLPG/SQL.

In your code it should be something like:

EXECUTE 'SELECT id FROM ' || tableref || ' WHERE somedate >= $1'
INTO c
USING hasta;

answered Jun 1, 2018 at 13:13

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

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

1 Comment

FiruzzZ Over a year ago

thanks, I was in the right path after all FOR r IN EXECUTE ... LOOP

Collectives™ on Stack Overflow

using variable as part of the statement

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related