Обсуждение: Update function

I'm having trouble with a function that on insert to a table needs to check
if the value is not 'No' and then needs to check if the code already exists
in the table, if the code does exist in the table, the value needs to be set
to 'R'.
Note that my_code is a varchar.

DROP FUNCTION check_code();
CREATE FUNCTION check_number() returns opaque as '
BEGIN
        IF tg_op = ''INSERT'' then
            IF new.my_code NOT LIKE ''No'' THEN
        IF new.my_code LIKE (SELECT my_code FROM permit WHERE my_code =
new.my_code) THEN
            UPDATE permit SET my_code LIKE ''R'' WHERE old.my_code LIKE new.my_code;
        end if;
            end if;
        end if;
    return null;
end;'
LANGUAGE 'plpgsql';

DROP trigger check_code_trg on permit;
create trigger check_code_trg before insert
on permit for each row execute procedure check_code();

If the user enters 'No' then the form submits fine, but for some reason the
data does not get inserted into the database, no error message, just no
record of it.  The same happens if a user enters a code (for my_code) that
hasn't alteady been entered into the permit table.

If the user enters a code already in the permit table I want it to update
all records containing that code to 'R', and insert the record with the
code.
This is the error message I get:
javax.servlet.ServletException: ERROR:  More than one tuple returned by a
subselect used as an expression.

"Sharon Cowling" <sharon.cowling@sslnz.com> writes:
> I'm having trouble with a function

(a) returning NULL from a before-trigger is a signal to the system to
skip the insert (or update or delete).  You need "return new" instead,
at least in the case where you want the insert to happen.

(b) this is pretty bizarre:

>     IF new.my_code LIKE (SELECT my_code FROM permit WHERE my_code = new.my_code) THEN

Perhaps you meant

    IF EXISTS(SELECT 1 FROM permit WHERE my_code = new.my_code) THEN

(c) this will have a syntax error whenever you finally reach it:

>     UPDATE permit SET my_code LIKE ''R'' WHERE old.my_code LIKE new.my_code;

In general you seem to be much too eager to use LIKE where = would do.
= is a lot cheaper, and isn't going to surprise you with odd behavior
on strings containing % or _ ...

            regards, tom lane