Cannot use variable with EXECUTE command in plpgsql function

Question

I have the following plpgsql code. I am trying to use r.a as one of the fields to insert into my new table, but it returns the error that r.a column does not exist.

create or replace function function1() returns void as $$
declare r t%rowtype;
begin
    EXECUTE format('CREATE TABLE f_t(a INT, b INT)');
    for r in select * from t
    LOOP
         EXECUTE format('INSERT INTO f_t(a, b) values (r.a, r.b)');
    END LOOP;
    RETURN;
end

t's structure is also (a int,b int).

you cannot to use plpgsql variables in dynamic query - it is executed in different context. Use `USING` clause — Pavel Stehule, Feb 28 '18 at 17:01

score 3 · Answer 1 · answered Feb 27 '18 at 23:40

Function variables and parameters are not visible within EXECUTE. (Hence the error message "column r.a does not exist".) You have to pass values, best with the USING clause. This would work:

CREATE OR REPLACE FUNCTION function1()
  RETURNS void AS
$func$
DECLARE
   r t%rowtype;
BEGIN
   EXECUTE format('CREATE TABLE f_t(a INT, b INT)');
   FOR r IN
      SELECT * FROM t
   LOOP
      EXECUTE format('INSERT INTO f_t(a, b) SELECT $1.*') -- !!
      USING r;
   END LOOP;
   RETURN;
END
$func$  LANGUAGE plpgsql;

But it does not make a whole lot of sense, since you can simply replace it with CREATE TABLE AS:

CREATE TABLE f_t AS TABLE t;

Cannot use variable with EXECUTE command in plpgsql function

1 Answers1

Linked