How can a Postgres SQL script be automatically run when a new table turns up?

When a user load a new table in the Postgres System? Can a script

automatically detect it and run?

Are you looking for Even triggers?

https://www.postgresql.org/docs/current/event-triggers.html

Regards,
Jay

Hi, Jay,

That looks interesting. Is there an excellent example to do the following?

How to create an event trigger in Postgres? When a user finished loading a
new table on to it, the trigger can start off an script 10 minutes after
the event?

Regards,

David

On Thu, 13 Jan 2022 at 10:50, Jayadevan M <maymala.jayadevan@gmail.com>
wrote:

On Thu, Jan 13, 2022 at 8:55 AM Shaozhong SHI <shishaozhong@gmail.com>
wrote:

That looks interesting. Is there an excellent example to do the following?

How to create an event trigger in Postgres? When a user finished loading
a new table on to it, the trigger can start off an script 10 minutes after
the event?

IIUC when an event trigger executes it prevents the DLL (create table in
this example) from committing - so the only thing the event trigger can
meaningfully do is write a record to the database that tells some other
piece of software to do something 10 minutes from now. You could have a
script execute every minute (say via cron) looking for such a record and
when it finds one, and the time is right, it would do its thing. Otherwise
it would just stop.

David J.

On Thu, Jan 13, 2022 at 4:32 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Thu, Jan 13, 2022 at 8:55 AM Shaozhong SHI <shishaozhong@gmail.com>
wrote:

How to create an event trigger in Postgres? When a user finished loading
a new table on to it, the trigger can start off an script 10 minutes after
the event?

You could have a script execute every minute (say via cron)

Vendor-specific, but on AWS, you can invoke a lambda from RDS or Aurora.

1. An event trigger function runs after CREATE TABLE and invokes a lambda.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html

2. The lambda calls StartExecution on a step function.
https://docs.aws.amazon.com/step-functions/latest/apireference/API_StartExecution.html

3. In the first step of the step functions, call wait for 10 minutes.
https://docs.aws.amazon.com/step-functions/latest/dg/amazon-states-language-wait-state.html

4. In the second step, have the lambda perform whatever operation(s) you
need to do.

No polling required, but you'd have to be running in a managed service in
the Amazon Cloud.

Similarly, if you are self-hosting and willing/able to write some C code or
run some pl/pythonu, you could create an extension/function that performs
this logic.

Or again if you are self-managed and go the cron route as suggested by
David Johnson, there's the extension pg_cron.

– Miles Elam