Postgresql – How to compare two last updated entities from two different table Postgresql

database-designpostgresqlquerytrigger

I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new table named as change and fill it by triggers again just before the price table will be updated.

Function:

create or Replace function test()
returns trigger
as
$$
begin

update change set change_= SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;
return new;

end;
$$
language plpgsql;

Trigger:

CREATE TRIGGER testtrig
BEFORE UPDATE
ON price
FOR EACH ROW 
EXECUTE PROCEDURE test();

I get error while using the querry –> SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 – SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;

Can you help me please?
Thanks

Best Answer

You are missing some parenthesis around your select queries.

But i don't understand completely what you try to achieve, i think you should at least also transfer the produdct_id, because for now you get a price from a unknown product.

create or Replace function test() returns trigger as $$ begin

update change 
set change_= (SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1) - (SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1) ; 
return new;

end; $$ language plpgsql;




CREATE TRIGGER testtrig BEFORE UPDATE ON price 
FOR EACH ROW 
  EXECUTE PROCEDURE test();

Related Solutions

Postgresql – Disable/enable PostgreSQL trigger inside function for the same table

One option would be to:

Jack up the security on the table so normal users can't directly access it at all, and remove the protecting trigger.
Create an updatable view on the table which is accessible by normal users, and add an INSTEAD OF trigger to protect the column from updates (assuming you want it still visible; if not, simply omit it completely from the view definition.)
Create a function with definer's rights which bypasses the view and directly updates the column in the base table.

Postgresql – Within a trigger is there a way to tell if an update or insert came from a trigger or not

There are several ways for determining if the trigger is recursing. Checking the trigger depth (pg_trigger_depth()) is not one of them, because you don't know if the trigger itself is recursing or is being fired from another trigger.

The only direct way to know analysing the execution stack during an exception:

DECLARE
  context text;
BEGIN
  BEGIN
    SELECT 1/0;
  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS context = PG_EXCEPTION_CONTEXT;
  END;
  -- analyse here the context variable to find if the last item in the stack
  -- is this same function
END;

Exception handling is costly, but so are the other options, which require leaving a session marker for avoiding recursion, either using a temporary table, or a session variable:

BEGIN
  IF to_regclass('pg_temp.' || quote_ident(TG_NAME)) THEN -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  EXECUTE format('CREATE TEMP TABLE %I (a int) ON COMMIT DROP', TG_NAME);
  -- general body of trigger
  EXECUTE format('DROP TABLE pg_temp.%I', TG_NAME);
  RETURN NEW; -- or whatever appropriate
END;

or using a session variable:

BEGIN
  IF current_setting('x.' || TG_NAME, true) IS DISTINCT FROM '1' THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  PERFORM set_config('x.' || TG_NAME, '1', true);
  -- general body of trigger
  PERFORM set_config('x.' || TG_NAME, '', true);
  RETURN NEW; -- or whatever appropriate
END;

All of the options above have a significant performance impact. A possibly better performing option could involve a table for this purpose:

CREATE TABLE trigger_execs (
  pid int NOT NULL DEFAULT pg_backend_pid(),
  tgname name NOT NULL,
  PRIMARY KEY (pid, tgname)
);

which can be used like:

BEGIN
  IF EXISTS (SELECT 1 FROM trigger_execs
             WHERE pid = pg_backend_pid() AND tgname = TG_NAME) THEN
    -- it's recursing
    RETURN NEW; -- or NULL, or whatever action is required when recursing
  END IF;
  INSERT INTO trigger_execs (tgname) VALUES (TG_NAME);
  -- general body of trigger
  DELETE FROM trigger_execs
  WHERE pid = pg_backend_pid() AND tgname = TG_NAME;
  RETURN NEW; -- or whatever appropriate
END;

Function:

Best Answer

Related Solutions

Postgresql – Disable/enable PostgreSQL trigger inside function for the same table

Postgresql – Within a trigger is there a way to tell if an update or insert came from a trigger or not

Related Question