Return to Answer

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

t and t1 being the tables with identical schema.

Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

• Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

• Polymorphic types

• Object identifier types

• Dynamic SQL in plpgsql

• VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

The value of the first parameter is discarded. Use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.

SQL Fiddle demo.

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR _tbl_in IN EXECUTE
      format('SELECT * FROM %s', pg_typeof(_tbl_in))
   LOOP
      -- do something with record

      EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
      USING _tbl_in;
   END LOOP;
END
$func$;

t and t1 being tables with identical schema.

The polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

• Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

• Polymorphic types

• Object identifier types

• Dynamic SQL in plpgsql

• VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

The value of the first parameter is discarded. Just use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter "Various complete table types".

db<>fiddle here
_{Old sqlfiddle}

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.

Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

• Insert values from a record variable into a subclass table

###Major ingredients

• Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

• Polymorphic types

• Object identifier types

• Dynamic SQL in plpgsql

• VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

I am handing in a NULL value of type t, which serves three purposes:

• Provide table name.
• Provide table type.
• Serve as loop variable.

The value of the first parameter is discarded. Use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.

SQL Fiddle demo.

If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.

Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

• Insert values from a record variable into a subclass table

Major ingredients

• Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

• Polymorphic types

• Object identifier types

• Dynamic SQL in plpgsql

• VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

I am handing in a NULL value of type t, which serves three purposes:

• Provide table name.
• Provide table type.
• Serve as loop variable.

The value of the first parameter is discarded. Use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.

SQL Fiddle demo.

If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.

Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

• Insert values from a record variable into a subclass table

###Major ingredients

• Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

• Polymorphic types

• Object identifier types

• Dynamic SQL in plpgsql

• VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

I am handing in a NULL value of type t, which serves three purposes:

• Provide table name.
• Provide table type.
• Serve as loop variable.

The value of the first parameter is discarded. Use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.

SQL Fiddle demo.

If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.

Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

• Insert values from a record variable into a subclass table

###Major ingredients

• Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

• Polymorphic types

• Object identifier types

• Dynamic SQL in plpgsql

• VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

I am handing in a NULL value of type t, which serves three purposes:

• Provide table name.
• Provide table type.
• Serve as loop variable.

The value of the first parameter is discarded. Use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.

SQL Fiddle demo.

If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.