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
– Bergi, Commented May 23, 2021 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
– Bergi, Commented May 23, 2021 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
– NevD, Commented May 23, 2021 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
– NevD, Commented May 23, 2021 at 12:58
There are many. Some dedicated tools, some built into frameworks or ORM libraries. — Bergi
– Bergi, Commented May 23, 2021 at 13:02

NevD · Accepted Answer · 2021-05-23 13:31:32Z

1

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();

edited May 23, 2021 at 13:31

answered May 23, 2021 at 12:57

NevD

3154 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert stored functions and triggers in a Postgres container

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related