Re: Spot the error in my plpgsql... - Mailing list pgsql-general

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