postgresql 插入到pg/gp函数中的select from

ctzwtxfj 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(115)

我是postgres的新手。我写了一个函数，它从另一个表按部分插入表中的数据（因为源表中的数据太多，无法一次完成）：

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 
;

字符串
获取SQL错误[42601]：错误：语法错误位于或接近“||“
查询时如何正确地连接模式和表名？我哪里做错了？
用途：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

postgresql

来源：https://stackoverflow.com/questions/76823876/insert-into-select-from-in-pg-gp-function

1条答案

按热度按时间

6bc51xsx1#

为此，您需要动态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 
;

个字符
fiddle

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 插入到pg/gp函数中的select from

1条答案

相关问题

热门标签

最新问答