Question 222791 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – insert into select from in pg/gp function

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search