plpgsql函数未按预期插入数据 [英] plpgsql function not inserting data as intended

问题描述

我成功编译了以下函数.当我执行select schema.funtion_name();时，该函数将被执行，但表schema.table_insert中没有插入任何行:

I have the below function compiled successfully. When I do select schema.funtion_name();, the function gets executed but there are no rows inserted in the table schema.table_insert:

CREATE OR REPLACE FUNCTION schema.function_name()
RETURNS void AS
$BODY$
DECLARE cur_1 CURSOR FOR
    Select col1 from schema.table1
    union
    select col1 from schema.table2
    union
    select col1 from schema.table3
    union
    select col1 from schema.table4; 

BEGIN
    FOR rec_i in cur_1 LOOP
        insert into schema.table_insert (col1,col2,col3) 
        select col1,col2,col3 
        from schema.view 
        where col1=rec_i.col1

        commit;
    END LOOP;     
END;
$BODY$
LANGUAGE plpgsql STABLE

光标cur_1中的选择返回超过900 000条记录.当我对单个记录单独使用insert语句时，记录将插入到表中.

The select in cursor cur_1 returns more than 900 000 records. When I use the insert statement separately for single record, the record gets inserted in the table.

推荐答案

我成功编译了以下函数.

I have the below function compiled successfully.

不，你没有.

对于初学者，plpgsql函数未编译".在创建时，仅执行表面语法检查，然后按原样存储函数主体.没有编译.后期绑定.嵌套的SQL语句被视为准备好的语句.

For starters, plpgsql functions are not "compiled". On creation, only superficial syntax checks are done, then the function body is stored as is. No compilation. Late binding. Nested SQL statements are treated as prepared statements.

此外，您显示的功能根本无法创建.这是语法上的废话. INSERT之后缺少分号. COMMIT没有意义，在plpgsql中是不允许的.您不需要为此的光标.也不会循环播放.使用简单的SQL语句:

That aside, the function you display cannot be created at all. It is syntactical nonsense. Missing semicolon after the INSERT. COMMIT does not make sense and is not allowed in plpgsql. You do not need a cursor for this. Nor looping. Use a simple SQL statement:

INSERT INTO schema.table_insert (col1, col2, col3) 
SELECT v.col1, v.col2, v.col3 
FROM   schema.view v
JOIN  (
   SELECT col1 FROM schema.table1
   UNION
   SELECT col1 FROM schema.table2
   UNION
   SELECT col1 FROM schema.table3
   UNION
   SELECT col1 FROM schema.table4; 
   ) sub USING (col1);

等效，可能更快:

INSERT INTO schema.table_insert (col1, col2, col3) 
SELECT v.col1, v.col2, v.col3 
FROM   schema.view v
WHERE  EXISTS (SELECT 1 schema.table1 WHERE col1 = v.col1)
OR     EXISTS (SELECT 1 schema.table2 WHERE col1 = v.col1)
OR     EXISTS (SELECT 1 schema.table3 WHERE col1 = v.col1)
OR     EXISTS (SELECT 1 schema.table4 WHERE col1 = v.col1);

可以包含在一个函数中，但是plpgsql太过强大了.而STABLE对于包含INSERT的函数将是错误的.我建议使用简单的SQL函数，VOLATILE是默认函数，并且对此是正确的.

Can be wrapped up in a function, but plpgsql is overkill. And STABLE, would be wrong for a function containing an INSERT. I suggest a plain SQL function and VOLATILE is the default and correct for this.

CREATE OR REPLACE FUNCTION schema.function_name()
  RETURNS void AS
$func$
INSERT ...
$func$  LANGUAGE sql;

这篇关于plpgsql函数未按预期插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！