What is the most efficient way to create this trigger in Postgres.
I'll present below a very simplified example to what I need for my purposes, but it is based on the same concept.
Consider, we have got the schema defined below:
CREATE TABLE items (
item_id int4,
part_no int4,
description text);
CREATE TABLE blacklist (
part_no int4,
reason text);
CREATE TABLE matches (
item_id int4,
part_no int4,
reason text);
Then, every time a new item is added, we check if it is on the blacklist (comparin开发者_如何转开发g the part_no), and if it is, we create a new entry on the matches table.
You'd want a before-insert or after-insert trigger on items
:
create trigger blacklist_matches after insert on items
for each row execute procedure check_blacklist();
Then the check_blacklist
function would look something like this:
create function check_blacklist()
returns trigger as $$
begin
insert into matches (item_id, part_no, reason)
select NEW.item_id, NEW.part_no, blacklist.reason
from blacklist
where blacklist.part_no = NEW.part_no;
return null;
end;
$$ language plpgsql;
The funny looking $$
is, more or less, the SQL version of a heredoc.
That wraps the "is it on the blacklist" check and the matches
insertion in one simple bit of SQL. The NEW
variable in a trigger is a reference to the new row that you're working with. If NEW.part_no
doesn't match anything in blacklist
, then the SELECT won't produce anything and the INSERT will not be executed.
Presumably you'd have an index on blacklist.part_no
(which looks like a PK anyway) so the above should be quick enough.
The PostgreSQL documentation is pretty good and has a whole section on stored procedures and triggers if you need a reference.
精彩评论