Re: Spot the error in my plpgsql...

On Tue, Jan 16, 2001 at 12:58:41PM +0100, Mirko Zeibig wrote:
> 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;

This seems to be what I want, but how does this differ from the 'EXECUTE'
syntax, which I find is not yet in released versions of Postgres? I'll try
this out...

> 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;

Yep, this is the other option. This database wasn't really designed
with any kind of formal referential integrity, so I felt that the more
ad-hoc stuff I was doing above would be more flexible.

--
Adam Haberlach |A cat spends her life conflicted between a
adam(at)newsnipple(dot)com |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500 |profound desire to avoid getting wet.