Re: Spot the error in my plpgsql...

On Mon, Jan 15, 2001 at 07:54:26PM -0800, Adam Haberlach wrote:
> I've got the following procedure...
>
> DROP FUNCTION "blank_referring_devices" ();
> CREATE FUNCTION "blank_referring_devices" () RETURNS opaque AS
> '
> BEGIN
> EXECUTE ''UPDATE t_device SET accountid=NULL WHERE accountid =''
> || quote_literal(OLD.accountid);
> END;
> '
> LANGUAGE 'plpgsql';
>
> DROP TRIGGER "t_account_blank_devrel" ON "t_account";
> CREATE TRIGGER "t_account_blank_devrel" BEFORE DELETE ON "t_account"
> FOR EACH ROW EXECUTE PROCEDURE "blank_referring_devices" ();

Hello Adam,
of course I do not know what you want exactly, but why do you need EXECUTE
for this?

BEGIN
UPDATE t_device
SET accountid=NULL
WHERE accountid=quote_literal(OLD.accountid);
END;

should do as well. Maybe you are even better of with a foreign key
constraint, where you may include 'on delete set null' as well.

alter table T_DEVICE
add constraint FK_T_DEVICE_ACCOUNTID
foreign key (ACCOUNTID)
references T_ACCOUNT(ACCOUNTID)
on delete set null;

Regards
Mirko