Re: assigning result of SELECT in TRIGGER

"Jeff Eckermann" <jeckermann@verio.net> writes:
> Anyone want to comment on whether this makes a difference, and why?

>> CREATE FUNCTION chk_status () RETURNS OPAQUE AS '
>> DECLARE
>> status BOOLEAN;
>> BEGIN
>> status := SELECT status FROM log WHERE log.log_no = NEW.log_no;

You can write the assignment as either

    status := status FROM log WHERE log.log_no = NEW.log_no;

(*no* SELECT keyword here) or

    SELECT INTO status status FROM log WHERE log.log_no = NEW.log_no;

(there's actually a good deal of freedom about where to put the INTO,
but I tend to like putting it first).

As Jeff points out, this isn't going to work as-is even when you get
past the simple syntactic error, because plpgsql is going to think that
all instances of "status" refer to its variable, not to fields of some
table.  Possible ways to handle this include:

1. Rename the plpgsql variable to not conflict with any fields you
want to access.  This is probably the least confusing way.

2. Explicitly qualify the conflicting field names.  For example
    status := log.status FROM log WHERE log.log_no = NEW.log_no;
would work.

3. Double-quote the field names.  I haven't tried this, but from looking
at the plpgsql sources I think this would work:
    status := "status" FROM log WHERE log.log_no = NEW.log_no;

            regards, tom lane