Update function

Поиск
Список
Период
Сортировка
От Sharon Cowling
Тема Update function
Дата
Msg-id 001a01c1627e$602c93a0$f902a8c0@fiji.sslnz.com
обсуждение исходный текст
Ответы Re: Update function  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
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.



В списке pgsql-novice по дате отправления:

Предыдущее
От: "Josh Berkus"
Дата:
Сообщение: Re: Returning data from function
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Returning data from function