Solved

[HELP] Stored Procedure, Trigger, Trigger Function

Posted on 2004-08-13
14
12,827 Views
Last Modified: 2012-06-27
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
Comment
Question by:engtat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 11791632
Once in fuinction public.log_update TRIGGER context does not exist.  So NEW.name does not compute, does not compute, does not compute .....
0
 
LVL 1

Author Comment

by:engtat
ID: 11806887
I do not understand, can you correct my code?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11821983
I could but I won't.
Use the documentation to implement s simple example and learn from that.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:engtat
ID: 11827081
I try to follow the documentation, but failed.
0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11827429
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
 
LVL 1

Author Comment

by:engtat
ID: 11827828
sorry I did not know the rules here, this is my second time here. I will increase the points now.
0
 
LVL 9

Expert Comment

by:rjkimble
ID: 11828220
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
 
LVL 1

Author Comment

by:engtat
ID: 11828837
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 140 total points
ID: 11848263
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
 
LVL 9

Expert Comment

by:rjkimble
ID: 11848830
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
 
LVL 22

Expert Comment

by:earth man2
ID: 11852941
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
 
LVL 9

Expert Comment

by:rjkimble
ID: 11853304
>> 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
 
LVL 1

Author Comment

by:engtat
ID: 11866716
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
 
LVL 9

Expert Comment

by:rjkimble
ID: 11867163
Amazing.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question