Plpgsql is getting confused by the double usage of the name "status", i.e.
as a variable, and as the name of a field.
Choosing a different name for your variable should work.
I think the usual syntax for this kind of operation is:
SELECT field INTO variable FROM....
I don't see why your construction wouldn't work, though.
Anyone want to comment on whether this makes a difference, and why?
----- Original Message -----
From: "Randall Perry" <rgp@systame.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, August 16, 2001 9:01 PM
Subject: [GENERAL] assigning result of SELECT in TRIGGER
> Upon inserting a record into the confirm table I get this error in psql:
>
> ERROR: parser: parse error at or near "SELECT"
>
> What am I doing wrong. Is there a proper way to get a value from a related
> table.
>
>
>
> The function and trigger defs are:
>
> CREATE FUNCTION chk_status () RETURNS OPAQUE AS '
> DECLARE
> status BOOLEAN;
> BEGIN
> status := SELECT status FROM log WHERE log.log_no = NEW.log_no;
> IF NOT status THEN
> RAISE EXCEPTION ''The log entry you are trying to confirm
> was not successfully forwarded and does not need confirmation'';
> END IF;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> CREATE TRIGGER chk_status BEFORE INSERT ON confirm FOR EACH ROW EXECUTE
> PROCEDURE chk_status();
>
> --
> Randy Perry
> sysTame
> Mac Consulting/Sales
>
> phn 561.589.6449
> mobile email help@systame.com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>