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
– Pavel Stehule, Commented Feb 28, 2018 at 17:01

Erwin Brandstetter · Accepted Answer · 2018-02-27 23:40:45Z

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;

Collectives™ on Stack Overflow

Cannot use variable with EXECUTE command in plpgsql function

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related