Thread: Spot the error in my plpgsql...
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" ();
And when I delete an account, I get the following:
test=# delete from t_account where accountid='6084751D55C11C3704B14FD4A87F3F7B';
ERROR: parser: parse error at or near "execute"
Any ideas?
--
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.
Adam Haberlach <adam@newsnipple.com> writes:
> ERROR: parser: parse error at or near "execute"
> Any ideas?
EXECUTE is a new feature in 7.1 ...
regards, tom lane
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;
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;
Regards
Mirko
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.
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
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.