Re: Spot the error in my plpgsql...

On Tue, Jan 16, 2001 at 12:22:28PM -0800, Adam Haberlach wrote:
> 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;
> > > '
> > 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...

If I am not mistaken, without EXECUTE tablenames etc. are "hard-compiled",
with EXECUTE you could do sth. like:

CREATE FUNCTION "blank_referring_devices" (text) RETURNS opaque AS
 DECLARE
     relname ALIAS FOR $1
 BEGIN
     EXECUTE ''UPDATE '' || relname || '' SET accountid=NULL WHERE accountid =''
  || quote_literal(OLD.accountid);
 END;


 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" ('t_device');


so you may pass the name of the table, for which accountid is to be
deleted. Nonetheless I'd do this with FOREIGN KEYS, though :-).

Note: I have not checked wether this works and wether text is adequate for
tablenames, maybe you have to convert this to char or varchar.

Regards
Mirko