MacLochlainns Weblog

This demonstrates how you insert results from a common table expression (CTE) in a leading WITH clause. I thought it would be a nice add since the existing tutorials didn’t have an example.

Create the message table, like this:

CREATE TABLE message
( message_id    SERIAL
, message_text  VARCHAR );

Now, here’s a CTE with a two fabricated rows:

WITH cte AS
( SELECT 'x-ray' AS msg
  UNION ALL
  SELECT 'MRI' AS msg )
INSERT INTO message
( message_text )
( SELECT msg FROM cte );

Then, you can query the results from the message table, like this:

SELECT * FROM message;

You should get the following results:

 message_id | message_text 
------------+--------------
          1 | x-ray
          2 | MRI
(2 rows)

Unfortunately, if you re-ran that it would insert a duplicate set of rows. You can avoid that behavior by using correlation between the subquery in the WITH clause and target table of the INSERT statement.

The following only allows you to INSERT new rows in the table.

WITH cte AS
( SELECT 'x-ray' AS msg
  UNION ALL
  SELECT 'MRI' AS msg )
INSERT INTO message
( message_text )
( SELECT msg
  FROM   cte 
  WHERE  NOT EXISTS
          (SELECT NULL
           FROM   message 
           WHERE  message.message_text = cte.msg ));

You can add revision control column to the message table to verify there’s no change with subsequent attempts. The first step requires you create an independent message_rev_id_seq sequence, like this:

CREATE SEQUENCE message_rev_id_seq
  INCREMENT 1
  MINVALUE -9223372036854775807
  MAXVALUE  9223372036854775806
  START    -9223372036854765807;

You can alter the table by using the standard rev_id column name:

ALTER TABLE message
ADD COLUMN rev_id BIGINT NOT NULL DEFAULT NEXTVAL('public.message_rev_id_seq');

The ALTER statement will automatically add the first two values of the message_rev_id_seq to the existing rows. The ALTER statement is an immediate action because all Data Definition Language (DDL) commands are 1-Phase Commit operations.

The next step is to create an update_message_rev_id function that return a trigger. You do this using PL/pgSQL but you must specify the language value explicitly when returning a trigger data type, like:

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION update_message_rev_id()
RETURNS TRIGGER AS
$$
BEGIN
  NEW.rev_id = NEXTVAL(‘public.message_rev_id_seq’);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The last step requires you create an update_message_revision trigger that calls the update_message_rev_id function, like:

CREATE TRIGGER update_message_revision BEFORE UPDATE
  ON message FOR EACH ROW
  EXECUTE PROCEDURE update_message_rev_id();

You can test it by running the correlated INSERT statement again. You’ll find that no rows are touched by the INSERT statement because the revision numbers are unchanged. You should see these two rows:

 message_id | message_text |        rev_id        
------------+--------------+----------------------
          1 | x-ray        | -9223372036854765807
          2 | MRI          | -9223372036854765806
(2 rows)

As always, I hope this helps those looking for a example of how to solve a problem.