Insert into table using for loop

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:

• Passing a record as function argument PL/pgSQL
• Return value cross join

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;