How to insert stored functions and triggers in a Postgres container

Question

I have a containerized Postgres database and I am trying to create a function and a trigger: a simple notification on a new row insertion.

It may seem stupid but I am unable to find how to write the code, where should I push it to the Postgres container, and how to programmatically insert the code inside the container.

I can see the following file structure

Database/Schemas/public/
|---tables
|  |___requests (tablename)
|  |__Triggers
|
|___Functions

I only (vaguely) found a way to copy the code inside when using the /docker-entrypoint-initdb.d/ folder (a COPY in the Dockerfile).

This injection works only - as per the Docker/Postgres documentation - when a database is created. This means I have to drop and recreate the database. This is wrong as I can't make any change to a production database.

Furthermore, when I adjoin the other SQL commands to the CREATE TABLE command in the .sql file, then the order of execution of these commands leads to errors (unknown table...).

My init.sqlfile programmatically copied into the /docker-entrypoint-initdb.d/ folder is:

-- init_table.sql
CREATE TABLE IF NOT EXISTS public.requests (
    id serial PRIMARY KEY,
    ...,
);

-- create_function_notify.sql
CREATE OR REPLACE FUNCTION notify_insert() RETURNS trigger AS
$$
   BEGIN
      PERFORM pg_notify('new_notif', row_to_json(NEW)::text);
      RETURN NEW;
   END;
$$ LANGUAGE plpgsql;


-- create_trigger_new_event.sql
CREATE TRIGGER new_event
   AFTER INSERT
   ON public.requests
   FOR EACH ROW
   EXECUTE PROCEDURE notify_insert();

LANGUAGE plpgsql;

I also tried a "sh" version (dixit PG docs) without success: it throws an error around "$$".

#/bin/sh

set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE OR REPLACE FUNCTION notify_insert() RETURNS trigger AS
$$
  LANGUAGE plpgsql;
EOSQL

"*This injection works only when a database is created. This is wrong as I can't make any change to a production database.*" - to do that, you'll need some database migration tool. And it doesn't matter whether it runs inside the same docker container as the database or not. — Bergi, May 23 '21 at 12:30
"*When I adjoin the other SQL commands to the CREATE TABLE command in the .sql file, then the order of execution of these commands leads to errors (unknown table...)*" - nah, that should work. What is the exact error message? Notice you have a stray `LANGUAGE plpgsql;` in your sample snippet. — Bergi, May 23 '21 at 12:31
Indeed, in the ".sql" file, for the function, I changed the place to the `LANGUAGE plpgsql`, and removed the useless one for the trigger. To check, I opened a port on the container, used Dbeaver and I can see the trigger attached to the table, and the function. — NevD, May 23 '21 at 12:51
Could you elaborate on the database migration tool? Do you mean eg Sequelize for Node.js and ActiveRecord for Rails? How is this linked? — NevD, May 23 '21 at 12:58
There are many. Some dedicated tools, some built into frameworks or ORM libraries. — Bergi, May 23 '21 at 13:02
Thank you. Now I found this: https://stackoverflow.com/questions/41339056/sequelize-migration-create-trigger-in-postgresql — NevD, May 23 '21 at 13:14

NevD · Answer 1 · 2021-05-23T13:31:32.340

A Dockerfile instruction COPY ./pg_notify.sql /docker-entrypoint-initdb.d/ sets up the desired trigger and notification related function to the containerized Postgres database (on creation only).

Following Bergi's remark, the correct code is:


-- pg_notify.sql

CREATE TABLE IF NOT EXISTS public.requests (
    id serial PRIMARY KEY,
    app varchar,
    url varchar,
    ip varchar,
    host varchar,
    req_at VARCHAR,
    d BIGINT
);


CREATE OR REPLACE FUNCTION notify_insert() RETURNS trigger 
LANGUAGE plpgsql
AS $$
   BEGIN
      PERFORM pg_notify('new_notification', row_to_json(NEW)::text);
      RETURN NEW;
   END;
$$;

CREATE TRIGGER new_event
   AFTER INSERT
   ON requests
   FOR EACH ROW
   EXECUTE PROCEDURE notify_insert();

How to insert stored functions and triggers in a Postgres container

1 Answers1