0

I need to run some SQL scripts (which are basically SELECT queries) every time some new data enters the database.

When I say enters the database, I do not mean INSERT/UPDATE queries executed on an existing database/table(s), but rather every time a new database is created.

I am using PostgreSQL as my SQL DBMS.

I have tried many approaches but none of them suit my requirement -

  1. pgAgent/pg_cron - requires a time constraint to be specified. Can only be triggered periodically.
  2. SQL TRIGGER - can be applied only on a database (INSERT/UPDATE/DELETE trigger to check if a query has been executed on a database, i.e., assuming the database already exists)

I am yet to try the usage of a Procedural Language (PL/PgSQL, PL/PERL), but it would be nice if someone could point me in the right direction before I proceed.

TL;DR: I need to run a SQL script on a PostgreSQL installation every time a new database is created (on a given server).

sujaypatil
  • 60
  • 1
  • 10
  • what exactly does this SQL script do? –  Apr 19 '19 at 05:10
  • It's a simple SELECT query which runs on a specific table which will be present in the newly inserted database. – sujaypatil Apr 19 '19 at 07:33
  • If you want to define default table **statically**, template database might be helpful. This can't run SQL script though. https://www.postgresql.org/docs/10/manage-ag-templatedbs.html – s sato Apr 19 '19 at 08:28
  • Usually, the one that creates the database is also responsible for initializing it, if necessary. Why is that not an option for you? And if it is just about some `SELECT` queries where should the output go or do you discard it? Do the `SELECT` statements _trigger_ something inside the new created + initialized database? – Ancoron Apr 19 '19 at 18:46

0 Answers0