When inserting records into an SQL database using a existing table as a source usually means we need to know and specify the column structure of the target table. If we are trying to do this using a stored procedure then our script must be changed every-time the table’s schema changes.
Here is a way to simplify this job of duplicating records in a Postgres database.
Suppose you have a database with 2 tables.
So we know all the books in a library and which library has each book.
Now suppose we decide to add a new library and populate it with the same books in an existing library. We could iterate through a select on the books
SELECT * FROM books WHERE id=oldid;
oldid is the id of the existing library.
And for each record, we want to add a new record using
newid as the id of the new library. This is quite awkward; all we want to do is replace the old id with the new but we have to unpack all the columns of the books table and repacked them in a new insert; something like
= book.field1 field1 = book.field2 field2 = book.field3 field3 ... INSERT INTO books VALUES (newid, field1, field2, field3,.....);
This is quite tedious and if we want to achieve this programmatically means we have to specify the table structure of the books table is such a way that if the schema changes, we have to remember to go back and change this script as well.
But there is a better way:
Instead of stating the fields explicitly, we can use the
INSERT INTO table SELECT *
form provided we can figure a way of replacing the library id. The following
plpgsql script illustrates how to do this:
CREATE FUNCTION duplicatebooks (oldid INTEGER, newid INTEGER) RETURNS voidAS $$ DECLARE book books;BEGIN FOR book IN SELECT * FROM books WHERE id=oldid LOOP id = newid; book.INSERT INTO BOOKS SELECT book.*; END LOOP; END; $$ LANGUAGE plpgsql;
In this script:
In this way, we do not even need to know the other fields in the book table
In principle, we could have used a generic RECORD variable when iterating over the books table but unfortunately trying to pass a generic RECORD to the INSERT statement is explicitly disallowed and results in the error:
record type has not been registeredERROR:
The solution is to just declare a type for the record at the outset. So instead of having this fragment (which fails):
DECLARE RECORD; book BEGIN FOR book IN SELECT * FROM books WHERE id=oldid LOOP id = newid; book.INSERT INTO books SELECT book.*;
we do this instead
DECLARE book books;BEGIN FOR book IN SELECT * FROM books WHERE id=oldid LOOP id = newid; book.INSERT INTO books SELECT book.*;