CODEWITHSUNDEEP
postgresqlsql-insertplpgsqldynamic-sql
Chris
Chris

Reputation: 1421

Cannot use variable with EXECUTE command in plpgsql function

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).

Upvotes: 1

Views: 676

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

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;

Related:

Upvotes: 3

Related Questions