Blog / 2015 / January / 12

Simple PostgreSQL Scripting

January 12, 2015

I wanted to write a postgresql script that would insert into two tables, and it would include the autogenerated serial from the first insert in the second. I have a master/detail relationship so the serial from the first insert (the master table) needs to be used in multiple rows in the second (detail) table.

This can be done using the following code. The variable table_serial is declared, and then we do the first insert with RETURNING id INTO table_serial to get the value. We then just use this value in subsequent insert:

DO $$
DECLARE table_serial bigint;
BEGIN
   INSERT INTO drum_master (ptb_serial, ptb_table_name, ptb_unique_identifier) 
          VALUES (null,'BATCH_014_B',null) 
          RETURNING id INTO table_serial;

   INSERT INTO drum_detail (pcl_serial, pcl_table_name, pcl_column_name, pcl_pan_table_serial) 
          VALUES (null,'BATCH_014_B','KEY_SEQUENCE',table_serial);
   INSERT INTO drum_detail (pcl_serial, pcl_table_name, pcl_column_name, pcl_pan_table_serial) 
          VALUES (null,'BATCH_014_B','SOURCE_ROW',table_serial);
END $$

Tags: postgres script insert id