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

On Wed, Jan 17, 2001 at 01:03:05PM +0100, Mirko Zeibig wrote:
> 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 :-).

    This explains a lot--I've wanted to do that sort of thing before, and know that
I'll want to do it in the future, so it's glad to know that I'll be able to do
it with the new release...thanks for the good answers...

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

    Seems to be working for what we want, and I like I said, I prefer the
flexibility we get by doing it manually.  Unless FOREIGN KEYS is more
efficient in some way, I'll deal with the management of the functions
manually in order to be able to do some of the ad-hoc stuff that we are
going to be doing.

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