Обсуждение: 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.