plpgsql function not inserting data as intended

Question

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

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.

[So do you have your answer?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) — Erwin Brandstetter, Dec 28 '17 at 05:01

score 1 · Answer 1 · answered Dec 24 '17 at 04:55

I have the below function compiled successfully.

No, you haven't.

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.

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

Equivalent, may be faster:

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

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 function not inserting data as intended

1 Answers1

Linked