PostgreSQL 9.4 – How to Pass OLD and NEW to a Function in PL/pgSQL

functionsplpgsqlpostgresqlpostgresql-9.4

I've got 2 triggers. triggerA and triggerB that acts upon changes on tableA and tableB respectively. Inside both those triggers I want to use the same function that uses the OLD & NEW variables. I don't know how to do so because it throws me the error:

OLD hasn't been assigned yet.
The tuple structure of a not-yet-assigned record is indeterminate.

Then I tried assigning null to a variable to get around that and it's:

old_v hasn't been assigned yet.

trigger 1 & 2 (this part is common in both triggers):

DECLARE 
    added added_scores;
    old_v record;
    new_v record;
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            old_v = null;
            new_v = NEW;
        WHEN 'UPDATE' THEN
            old_v = OLD;
            new_v = NEW;
        WHEN 'DELETE' THEN
            old_v = OLD;
            new_v = null;
    END CASE;

    select _i_compute_added_scores(TG_OP, old_v, new_v) into added;
        -- some computation, return result

function :

CREATE OR REPLACE FUNCTION public._i_compute_added_scores(
    tg_op_p text,
    old_p anyelement,
    new_p anyelement)
  RETURNS added_scores AS
$BODY$

DECLARE
    new_score integer;
    old_score integer;
    res added_scores;
BEGIN
    CASE TG_OP_p
        WHEN 'INSERT' THEN
            new_score := new_p.score;
            old_score := 0;
        WHEN 'DELETE' THEN
            new_score := 0;
            old_score := old_p.score;
        WHEN 'UPDATE' THEN
            new_score := new_p.score;
            old_score := old_p.score;
    END CASE;

-- some more computation and return

Best Answer

You need to assign the RECORD type before sending it as parameter, and a very simple way to do that in your case would be simple assigning it to NEW or OLD even when it wouldn't be necessary, like:

CASE TG_OP
    WHEN 'INSERT' THEN
        old_v = NEW;
        new_v = NEW;
    WHEN 'UPDATE' THEN
        old_v = OLD;
        new_v = NEW;
    WHEN 'DELETE' THEN
        old_v = OLD;
        new_v = OLD;
END CASE;

Notice that now it is very important to use TG_OP and control which variables you must read/use.

Related Solutions

Postgresql – Storing logs on per day basis in PostgreSQL

The best option IMO is to use a cron job to generate the logs for 'yesterday'::date. You could also use triggers before insert/update/delete to update the other table but this adds complexity and overhead, and for the current day, but this gets pretty complicated. Generate your historical logs once the data won't change anymore.

In this case you write a sql query and run it via psql and cron.

I would also add a trigger denying update or delete to records covered in your historical data if you can.

This gives you a few benefits:

It is more obvious when it breaks
It is simpler, with simpler failure cases

Now, as per your concerns:

You say you need rows for every day. This can be handled a number of relatively easy ways in PostgreSQL (remember that dates support integer math so you can take a base date and add a series to it, to generate a date series). This is a pretty easy way to get around if you are generating rows per day of week, etc.
You say you cant guarantee things won't change. The key question here is what your change window is and to do your historical reports after this window has closed. For example if it is after a month, you can generate reports for all dates in a month a month prior (i.e. generate all dates in January during early March). You can then rely on a view to handle newer rows vs older rows in a live basis. You can then have a trigger which ensures that the date of an inserted row in the orders table is newer than the newest date in the other table.

In my experience worrying about keeping this as a live summary usually isn't necessary. Small organizations (with small data sets) tend to close out books at least once a year, and live reporting is an option there. Larger organizations with larger data sets tend to close out receivables and payables (i.e. invoices) once a month or so, and so the only areas that have to be reported live (because they are subject to adjustment or revision) are open orders (which can be revised) and invoices which may need to be reviewed occasionally (and should never be revised but may have adjustments issued against them which might or might not need to be tracked in such a system).

PL/SQL Beginner – Return Row Value from Dynamic SQL Function

For a single row, use EXECUTE IMMEDIATE ... INTO. A simple example:

set serveroutput on
declare
  l_result number;
begin
  execute immediate 'select count(*) from dual' into l_result;
  dbms_output.put_line(l_result);
end;
/

1

For multiple rows, use EXECUTE IMMEDIATE .. BULK COLLECT INTO, here is an example:

https://stackoverflow.com/questions/21117021/bulk-collect-into-and-execute-immediate-in-oracle

 declare
   type x is table of t.id%type index by pls_integer;
   xx x;
  begin
   execute immediate
   'select id from t' bulk collect into xx;
   dbms_output.put_line(xx.count);
 end;
 /

Ok, another example, with the 12c WITH function:

with function f1 (p_a number) return number
as
  l_result number;
begin
  execute immediate 'select count(*) from dba_objects where object_id = :B1' 
  into l_result
  using p_a;
  return l_result;
end;
select f1(object_id) from dba_objects where object_name = 'HELP';
/

F1(OBJECT_ID)
-------------
            1

Best Answer

Related Solutions

Postgresql – Storing logs on per day basis in PostgreSQL

PL/SQL Beginner – Return Row Value from Dynamic SQL Function

Related Question