From: | "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com> |
---|---|
To: | Scott Holmes <sholmes(at)pacificnet(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Triggers with arguments |
Date: | 2000-07-13 01:44:04 |
Message-ID: | 0007122151080F.01354@comptechnews |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 12 Jul 2000, Scott Holmes wrote:
> 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
Maybe try it like this:
CREATE FUNCTION del_stxnoted() RETURNS OPAQUE AS '
BEGIN
DELETE FROM stxnoted WHERE filename = OLD.fname AND record_key = OLD.rkey;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER del_location_trig
AFTER DELETE ON location FOR EACH ROW
EXECUTE PROCEDURE del_stxnoted();
- Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2000-07-13 01:46:15 | Re: Figured it out (psql and Gnu readline) |
Previous Message | Erich | 2000-07-13 01:18:24 | How to get PL languages to work in Postgres |