• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12892
  • Last Modified:

[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
0
engtat
Asked:
engtat
  • 5
  • 5
  • 4
1 Solution
 
earth man2Commented:
Once in fuinction public.log_update TRIGGER context does not exist.  So NEW.name does not compute, does not compute, does not compute .....
0
 
engtatAuthor Commented:
I do not understand, can you correct my code?
0
 
earth man2Commented:
I could but I won't.
Use the documentation to implement s simple example and learn from that.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
engtatAuthor Commented:
I try to follow the documentation, but failed.
0
 
rjkimbleCommented:
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.
0
 
engtatAuthor Commented:
sorry I did not know the rules here, this is my second time here. I will increase the points now.
0
 
rjkimbleCommented:
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.
0
 
engtatAuthor Commented:
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.
0
 
earth man2Commented:
drop table tbltest;

CREATE TABLE tbltest
(
 test_id int2 primary key,
 name text
);

drop table tblresult;
CREATE TABLE tblresult
(
 result_id serial,
 name text,
 found_indicator bool,
 position int8
);


CREATE OR REPLACE FUNCTION public.log_update()
 RETURNS trigger AS
'BEGIN
    UPDATE tblresult SET found_indicator = TRUE WHERE name = NEW.name;
RETURN NULL;
END;
'
 LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tr_log_update
 BEFORE INSERT
 ON public.tbltest
 FOR EACH ROW
 EXECUTE PROCEDURE public.log_update();

insert into tblresult( name, found_indicator, position ) values ( 'beta', false, 1 );

INSERT INTO tbltest VALUES (123, 'beta');

INSERT 0 0
select * from tblresult;
result_id | name | found_indicator | position
-----------+------+-----------------+----------
         1 | beta | t               |        1
(1 row)


0
 
rjkimbleCommented:
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.
0
 
earth man2Commented:
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;
0
 
rjkimbleCommented:
>> 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.
0
 
engtatAuthor Commented:
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!
0
 
rjkimbleCommented:
Amazing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now