PostgreSQL – How to Create an Everyday Trigger

postgresqlscripting

I want to add a function like trigger to my Postgres database. The problem is that this function should executed one time each day automatically and change values in the specified table.

Is it possible to carry out this by SQL syntax or is it better to write a bash script with some queries I need?

Best Answer

PostgreSQL does not have time-based triggers.

If you want to store as much of your logic as possible in the database, you could create an ordinary function (not a trigger function) and then invoke it from your favorite job scheduler (cron for me). If you want to use the database for storing data, not logic, then you could put the queries you want to run in a .sql file and use your favorite job schedule to invoke that file using psql -f for example. Or, if you want all of your application logic in, for example, a Perl module, you could write a function in your Perl module which connects to the database (reusing whatever connection logic your module already has) and does what you want, then use your favorite job scheduler to invoke that function.

There are many ways to do it, the key is to pick a method which fits in with the your overall design.

It would probably be possible to put the logic into a trigger on a dummy table, then have your favorite job scheduler do a dummy operation on the dummy table to get the trigger to fire. Pointless, but possible.

Related Solutions

PostgreSQL 8.4 – Create Trigger Hangs

Cannot be done with PG 8.4. It's actually on the TODO of postgres to reduce trigger requirements:

PostgreSQL – Using Trigger Function with Current Row and Table Name

Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this:

CREATE OR REPLACE FUNCTION trfn_tbl_log_any()
  RETURNS trigger AS
$func$
DECLARE
   _ct int;
BEGIN

IF NEW.timetype = 'start' THEN

   EXECUTE format($$
      SELECT floor(t.timeidx) + 1
      FROM   %s t
      WHERE  t.fnname = $1
      AND    t.timetype = 'start'
      ORDER  BY t.stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass  -- concatenate *identifer* ..
      )
   USING NEW.fnname        -- .. but pass *value* in USING clause
   INTO  NEW.timeidx;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      NEW.timeidx := 1;
   END IF;
END IF;

RETURN NEW;
END
$func$  LANGUAGE plpgsql;

All of this should work in Postgres 9.1. But consider upgrading to a current version anyway (currently 9.4).

NEW is not visible inside EXECUTE. Use the USING clause to pass the value from the new row (NEW.fnname).
Use TG_RELID (or TG_TABLE_SCHEMA and TG_TABLE_NAME) to concatenate the table name like we worked out under your previous question:
- PostgreSQL trigger function that selects from %current% table
Use format() and dollar-quoting to simplify the syntax of string-concatenation safely.
You can assign individual columns of the NEW row directly from the dynamic query.
Use GET DIAGNOSTICS _ct = ROW_COUNT; to check if rows were found. Per documentation:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

Aside: the logic of the dynamic query is only correct for BEFORE trigger. An AFTER triggers would also see the newly inserted row.

Best Answer

Related Solutions

PostgreSQL 8.4 – Create Trigger Hangs

PostgreSQL – Using Trigger Function with Current Row and Table Name

Related Question