insert into select from in pg/gp function

Question

I'm Newbie in postgres. I wrote a function, which insert into table a data by portions(because there is too much data in the source table to do it all at once) from another table:

CREATE OR REPLACE FUNCTION data_load(p_src_schema character varying, p_src_tab character varying,p_tgt_schema character varying, p_tgt_tab character varying)
  RETURNS void AS
$BODY$
DECLARE
    table_rec record;
BEGIN
    FOR table_rec IN
        SELECT otchdor, descr
        FROM otchdor
        ORDER BY otchdor
    loop
        insert into p_tgt_schema||'.'||p_tgt_tab
        select from p_src_schema||'.'||p_src_tab
        where  otchdor = table_rec.otchdor;
    end loop;
    return;
END;
$BODY$
  LANGUAGE plpgsql 
;

got SQL Error [42601]: ERROR: syntax error at or near "||"

How correctly concat schema and table names at query? Where did I go wrong?

use: PostgreSQL 9.4.24 (Greenplum Database 6.14.0 build commit:Open Source) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit

nbk · Answer 1 · 2023-08-03T10:15:02.563

For that you need dynamic sql

CREATE OR REPLACE FUNCTION data_load(p_src_schema character varying, p_src_tab character varying,p_tgt_schema character varying, p_tgt_tab character varying)
  RETURNS void AS
$BODY$
DECLARE
    table_rec record;
BEGIN
    FOR table_rec IN
        SELECT otchdor, descr
        FROM otchdor
        ORDER BY otchdor
    loop
        EXECUTE  'insert into ' || quote_nullable(p_tgt_schema) || '.' || quote_nullable(p_tgt_tab) ||
        ' select * from ' || quote_nullable(p_src_schema) || '.' || quote_nullable(p_src_tab)  ||
        ' where  otchdor = ' || quote_nullable(table_rec.otchdor);
    end loop;
    return;
END;
$BODY$
  LANGUAGE plpgsql 
;

CREATE FUNCTION

fiddle

Your code is SQL injection vulnerable. When you use dynamic SQL, then all text type parameters should be escaped. — Pavel Stehule, Aug 03 '23 at 06:45

score 0 · Answer 2 · answered Aug 04 '23 at 05:27

Assuming Greenplum copied Postgres 9.4 functionality for PL/pgSQL functions. I don't actually use Greenplum.

Use dynamic SQL with EXECUTE. Best with format() to concatenate the query string comfortably and safely:

CREATE OR REPLACE FUNCTION data_load(p_src_schema text, p_src_tab text, p_tgt_schema text, p_tgt_tab text)
  RETURNS void
  LANGUAGE plpgsql STRICT AS
$func$
DECLARE
   rec record;
BEGIN
   FOR rec IN
      SELECT otchdor
      FROM   otchdor
      ORDER  BY otchdor
   LOOP
      EXECUTE format (
        'INSERT INTO %I.%I
         SELECT * FROM %I.%I
         WHERE  otchdor = $1'
       , p_tgt_schema, p_tgt_tab, p_src_schema, p_src_tab)
      USING rec.otchdor;
   END LOOP;
END
$func$;

The format specifier %I is for identifiers and correlates to quote_ident(). Either double-quotes identifiers safely as needed, to defend against SQL injection and syntax errors. quote_nullable() (like has been suggested) would be nonsense for identifiers. Also, best pass the value as (typed) value with the USING clause See:

Also, the DDL command would fail if any identifier was null, so I made the function STRICT. The manual:

If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

But I don't think this function makes much sense to begin with. Chopping up that big INSERT would make sense with separate transactions (like you could employ in a PROCEDURE in modern Postgres, but not in Greenplum). A function always runs inside a single transaction anyway.

insert into select from in pg/gp function

2 Answers2