Insert into table using for loop

Question

I am trying to write a loop that will read a staging table and then consequently insert that record into a another table. The reason I need to do single record processing is that I need to find the value of the Sequence after every insert into the target table. I haven't added that functionality to the SQL (that will come later). I am having difficulty inserting the records into the target table.

I am getting a syntax error, but I think there is more that needs to be fixed.

CREATE OR REPLACE FUNCTION test_insert()
  RETURNS SETOF varchar AS
$func$
DECLARE
    rec record;
BEGIN   
   FOR rec IN
        SELECT * FROM   TBL_stg01
   LOOP INSERT  INTO target_tbl
        SELECT * FROM TBL_stg01
;
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

This appears to be interacting with dome kind of database, yet you have neglected to identify which. — Scott Hunter, Dec 15 '14 at 15:26
You might want to identify what the syntax error is, if you'd like help in how to fix it. — Scott Hunter, Dec 15 '14 at 15:27
No need for a loop or a function: `insert into target_tbl select * from tbl_stg01 returning id`. — , Dec 15 '14 at 15:28
I see an `END IF` statement, but no `IF`; I see a `FOR` statement, but no `END FOR`. — Scott Hunter, Dec 15 '14 at 15:29
@ScottHunter: There is no `END FOR`. Only the `LOOP` needs an `END LOOP`. You're right about the `END IF`, though. — Erwin Brandstetter, Dec 16 '14 at 11:03

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

PL/pgSQL function audited

There are a couple of problems:

CREATE OR REPLACE FUNCTION test_insert()
   RETURNS SETOF varchar AS   -- what do you want to return?
$func$
DECLARE
    rec record;
BEGIN   
   FOR rec IN
      SELECT col1, col2, col3 FROM tbl_stg01  -- only needed columns
   LOOP
      INSERT INTO target_tbl (col1, col2, col3)
      VALUES (rec.col1, rec.col2, rec.col3)
      -- SELECT (rec).*  -- possible shorthand
   END LOOP;
END
$func$  LANGUAGE plpgsql;

If tbl_stg01 and target_tbl share the same row type, and you wanted to INSERT all columns from the source table you could work without an explicit target list. But since you obviously have an auto-incremented id, you most probably can't. You shouldn't in any case. Like Joe already provided.
Note how I only select needed columns in the first place.
You had an END IF without starting an IF block.
A function that writes to tables cannot be declared STABLE. I removed the keyword, so the default function volatility VOLATILE kicks in.
Your function is declared as RETURNS SETOF varchar but you are not returning anything ...

Proper solution

You mention that you need to find the value of the Sequence after every insert, but you fail to provide your table definition, clarify which sequence or what you need it for.

Either way, this is how you return automatically generated default values after an INSERT

INSERT INTO target_tbl (col1, col2, col3)
SELECT col1, col2, col3
FROM   tbl_stg01
RETURNING *

Related answer:

How to select inside a FOR-Loop for further computations?

You could chain this with more commands in a single statement with data-modifying CTEs like

WITH ins AS (
   INSERT INTO target_tbl (col1, col2, col3)
   SELECT col1, col2, col3
   FROM   tbl_stg01
   RETURNING target_tbl_id
   )
INSERT INTO tbl3 (some_id)
SELECT target_tbl_id
FROM   ins;

More examples with explanation:

score 0 · Answer 2 · answered Dec 15 '14 at 15:42

You need to mention your column names specifically in the insert, and you don't need to "reselect" your data.

CREATE OR REPLACE FUNCTION test_insert()
  RETURNS SETOF varchar AS
$func$
DECLARE
    rec record;
BEGIN   
   FOR rec IN
        SELECT * FROM   TBL_stg01
   LOOP INSERT  INTO target_tbl (colaname1, colname2, colname3...)
        values (rec.col1, rec.col2, rec.col3...);
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

Insert into table using for loop

2 Answers2

PL/pgSQL function audited

Proper solution