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@newsnipple.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.