CODEWITHSUNDEEP
postgresqlsql-insertplpgsql
sathyesh
sathyesh

Reputation: 1

PL/pgSQL function not inserting data as intended

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.

Upvotes: 0

Views: 744

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

I have the below function compiled successfully.

No, you haven't.

PL/pgSQL functions are not "compiled". On creation, only superficial syntax checks are done, the function body is stored as is. No compilation. Late binding. Nested SQL statements are treated like prepared statements.

That aside, the function you display cannot be created at all. It is syntactical nonsense. Missing semicolon after the INSERT. COMMIT is not allowed in a PL/pgSQL function. 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, faster:

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

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

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

Upvotes: 2

Related Questions