MacLochlainns Weblog

The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. You use the following syntax:

GRANT CREATE ON DATABASE videodb TO student;

As the student user, you create the app schema with the following syntax:

CREATE SCHEMA app;

Then, you can query the result as follows:

SELECT   * 
FROM     pg_catalog.pg_namespace
ORDER BY nspname;

You should see the following:

      nspname       | nspowner |               nspacl                
--------------------+----------+-------------------------------------
 app                |    16390 | 
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 pg_temp_1          |       10 | 
 pg_toast           |       10 | 
 pg_toast_temp_1    |       10 | 
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
(7 rows)

If you create a revision_history table without a schema name, it is automatically placed in the public schema, which means an attempt to describe the table will return an error. For example, you create a revision_history table with the following command:

CREATE TABLE app.revision_history
( revision_history_id serial
, session_id          VARCHAR
, TABLE_NAME          VARCHAR
, revision_id         INTEGER );

You describe the revision_history table with the following command:

\d revision_history

It will show the following because there is no revision_history table in the public schema and the default search path only include a schema that shares the name with the student user and the public schema.

Did not find any relation named "revision_history".

You can show the search path with the following:

show search_path;

It should return the following, which is a schema that shares the user’s name and public.

   search_path   
-----------------
 "$user", public
(1 row)

You set the search path as follows:

SET search_path TO app, "$user", public;

After you set the search_path, a standard attempt to describe the table will find the table whether it is in the app or public schema. That means the following command:

\d revision_history

Shows:

                                                  Table "app.revision_history"
       Column        |       Type        | Collation | Nullable |                            Default                            
---------------------+-------------------+-----------+----------+---------------------------------------------------------------
 revision_history_id | integer           |           | not null | nextval('revision_history_revision_history_id_seq'::regclass)
 session_id          | character varying |           |          | 
 table_name          | character varying |           |          | 
 revision_id         | integer           |           |          |

As always, I hope this helps somebody looking for an answer.

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.