Triggers with arguments

From: Scott Holmes <sholmes(at)pacificnet(dot)net>
To: pgsql-general(at)postgresql(dot)org (PG-General)
Subject: Triggers with arguments
Date: 2000-07-12 22:28:00
Message-ID: 200007122228.PAA14007@scotts.mynetwork.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm afraid I just don't see how this is done without being able to pass
arguments to the procedure or actually running an additional SQL statement
within the trigger:

I have a "notes" table that is potentially linked to records in many other
tables. The primary key is created from 1) the name of the table, and 2) the
primary key value of that table. When one of these records, with notes, is
deleted, I need to make sure that the notes records are deleted as well.

I've been playing around with this and perhaps my code that doesn't work will
illustrate what I need to accomplish and perhaps one of you kind readers will
show me just how to do it.

CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
DECLARE
fname alias for $1;
rkey alias for $2;
BEGIN
delete from stxnoted where filename = fname
and record_key = rkey;
END;'
LANGUAGE 'plpgsql';

create trigger del_location_trig
after delete
on location
for each row
execute procedure del_stxnoted("location", 'old.wher');

Postgres will not create this trigger as it does not recognize the function
del_stxnoted as actually existing.

I am attempting to convert a large suite of applications that currently run on
an Informix database. The syntax for this is

create trigger del_location delete on location referencing
old as pre_del
for each row
(
delete from stxnoted where ((filename = 'location' ) AND (record_key
= pre_del.wher ) ) );

Thanks, Scott

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Travis Bauer 2000-07-12 22:33:09 sql question
Previous Message Giles Lean 2000-07-12 21:58:43 Re: RE: [HACKERS] pg_dump & blobs - editable dump?