[HELP] Stored Procedure, Trigger, Trigger Function
Platform: Postgre 7.x and PgAdmin III
tbltest table
Code:
CREATE TABLE public.tbltest
(
test_id int2 NOT NULL DEFAULT 0,
name text,
CONSTRAINT tbltest_pkey PRIMARY KEY (test_id)
) WITH OIDS;
tblresult table
Code:
CREATE TABLE public.tblresult
(
result_id int2 NOT NULL DEFAULT 0,
name text,
found bool,
position int8,
CONSTRAINT tblresult_pkey PRIMARY KEY (result_id)
) WITH OIDS;
trigger
Code:
CREATE TRIGGER tr_log_update
BEFORE INSERT
ON public.tbltest
FOR EACH ROW
EXECUTE PROCEDURE public.log_update();
trigger function
Code:
CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
UPDATE tblresult SET found = TRUE WHERE name = NEW.name;
RETURN NULL;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
Here is what I want,
1. Insert a new entry to table tbltest with id and name
2. Trigger started, run stored procedure.
3. If the name on table tblresult is found and match, the flag(boolean) will set to true.
Error when run this SQL
INSERT INTO tbltest VALUES (123, 'beta');
Code:
WARNING: Error occurred while executing PL/pgSQL function log_update
WARNING: line 2 at SQL statement
ERROR: parser: parse error at or near "$1" at character 23
Thanks in advance
tbltest table
Code:
CREATE TABLE public.tbltest
(
test_id int2 NOT NULL DEFAULT 0,
name text,
CONSTRAINT tbltest_pkey PRIMARY KEY (test_id)
) WITH OIDS;
tblresult table
Code:
CREATE TABLE public.tblresult
(
result_id int2 NOT NULL DEFAULT 0,
name text,
found bool,
position int8,
CONSTRAINT tblresult_pkey PRIMARY KEY (result_id)
) WITH OIDS;
trigger
Code:
CREATE TRIGGER tr_log_update
BEFORE INSERT
ON public.tbltest
FOR EACH ROW
EXECUTE PROCEDURE public.log_update();
trigger function
Code:
CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
UPDATE tblresult SET found = TRUE WHERE name = NEW.name;
RETURN NULL;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
Here is what I want,
1. Insert a new entry to table tbltest with id and name
2. Trigger started, run stored procedure.
3. If the name on table tblresult is found and match, the flag(boolean) will set to true.
Error when run this SQL
INSERT INTO tbltest VALUES (123, 'beta');
Code:
WARNING: Error occurred while executing PL/pgSQL function log_update
WARNING: line 2 at SQL statement
ERROR: parser: parse error at or near "$1" at character 23
Thanks in advance
earth man2
Once in fuinction public.log_update TRIGGER context does not exist. So NEW.name does not compute, does not compute, does not compute .....
ASKER
I do not understand, can you correct my code?
I could but I won't.
Use the documentation to implement s simple example and learn from that.
Use the documentation to implement s simple example and learn from that.
ASKER
I try to follow the documentation, but failed.
I think the problem is that what you want done appears to require a relatively substantial development effort (maybe a couple hours), and this is really just a forum for experts to offer some help. If an expert has access to substantially similar code, then it might make sense to offer it in an answer, but writing and testing a bunch of code for some points is probably not worthwhile. Even you don't seem to place much value on the effort, inasmuch as you are assigning it a meager 50 points. However, even for 500 points I wouldn't want to tackle this one. It's just too much effort. Mayber another expert has already done something similar or has a better approach and will be able to help you.
You might want to consider finding and hiring a local consultant to assist you.
You might want to consider finding and hiring a local consultant to assist you.
ASKER
sorry I did not know the rules here, this is my second time here. I will increase the points now.
OK. I found your problem. It turns out that earthman2's idea was wrong, which I didn't expect, because he's usually right. "NEW" DOES EXIST in the trigger context. However, you have made a very bad choice in naming one of your columns "found" -- it's a PostgreSQL reserved word in PL/PGSQL. I suggest strongly that you change the name of that column. I tried fixing your version by quoting the column name, but all my efforts along those lines failed. However, I found a clumsy way that works, but it's very fragile:
CREATE OR REPLACE FUNCTION public.log_update( ) RETURNS trigger AS '
BEGIN
EXECUTE ''UPDATE tblresult SET found = TRUE WHERE name = '''' + NEW.name + '''''';
RETURN NULL;
END;
' LANGUAGE 'plpgsql' VOLATILE;
This works because "found" is now part of the string that you execute dynamically. Unfortunately, NEW doesn't exist in the context of that command, so you have to compute the entire string that you want the server to execute. As I said, however, I think this is a VERY BAD approach. I STRONGLY RECOMMEND that you rename the "found" column in your tblresult table.
CREATE OR REPLACE FUNCTION public.log_update( ) RETURNS trigger AS '
BEGIN
EXECUTE ''UPDATE tblresult SET found = TRUE WHERE name = '''' + NEW.name + '''''';
RETURN NULL;
END;
' LANGUAGE 'plpgsql' VOLATILE;
This works because "found" is now part of the string that you execute dynamically. Unfortunately, NEW doesn't exist in the context of that command, so you have to compute the entire string that you want the server to execute. As I said, however, I think this is a VERY BAD approach. I STRONGLY RECOMMEND that you rename the "found" column in your tblresult table.
ASKER
OK, I will try your solution, I know 'found' is really a bad naming, but this table I created purposely to try how stored procedure works in postgreSQL.
I will update here, and assign the points if it works for me, thanks in advance.
I will update here, and assign the points if it works for me, thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This script and the resulting output demonstrates conclusively that the "found" column is the source of the problem, just as I indicated in my earlier comment. It also demonstrates that the first comment about the trigger context was wrong, which I also indicated in my earlier comment. Note that the result_id column in the tblresult table has been changed from int2 to serial, but that doesn't really change the validity of the demonstration.
Note also that no rows will ever be entered into the tbltest table, because the trigger always returns NULL, which causes PostgreSQL to abort the insert.
Note also that no rows will ever be entered into the tbltest table, because the trigger always returns NULL, which causes PostgreSQL to abort the insert.
I guess you realliy want to insert data into tblresult then you return NEW not null.
CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
>> I guess you realliy want to insert data into tblresult then you return NEW not null.
Exactly. My guess is that either engtat doesn't want to insert data into tbltest or that the proposed trigger has been written for testing purposes only. Another approach could be this:
CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
IF FOUND THEN
RETURN NEW;
ELSE
RETURN NULL;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
This version causes the new row to be inserted unless the name value is not found in the tblresult table.
Exactly. My guess is that either engtat doesn't want to insert data into tbltest or that the proposed trigger has been written for testing purposes only. Another approach could be this:
CREATE OR REPLACE FUNCTION public.log_update()
RETURNS trigger AS
'BEGIN
UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
IF FOUND THEN
RETURN NEW;
ELSE
RETURN NULL;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
This version causes the new row to be inserted unless the name value is not found in the tblresult table.
ASKER
Thanks for everyone! rjkimble and earthman2 provided me very good explaination, unfortunately I cannot reward you guys at same time. I just assigned all my points to earthman2 because he was the first answering me from plain to full answer. thanks ya!
Amazing.