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