Skip site navigation (1) Skip section navigation (2)

Update function

From: "Sharon Cowling" <sharon(dot)cowling(at)sslnz(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Update function
Date: 2001-11-01 02:39:04
Message-ID: 001a01c1627e$602c93a0$f902a8c0@fiji.sslnz.com (view raw or flat)
Thread:
Lists: 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.



Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-11-01 04:19:40
Subject: Re: Returning data from function
Previous:From: Tatsuo IshiiDate: 2001-11-01 01:01:15
Subject: Re: CP1250 to and from Unicode conversion, how?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group