Thread: Trigger and Trigger function, Part 3 - success!
Thanks Mr. Fuhr, I used the psql and it worked. The pgAdmin tool must have been having trouble with my schema qualified SQLcode. The trigger and trigger function work properly now. Thanks again for the more generic function - I will have manytables that will be using a last_touch column and will be using that function. Hmmm... I saw the cursor you mention in my screenshot. I cannot remember if I put it there or if the pgAdmin tool put itthere. The latter would be a nice debugging feature. Thanks everybody for all the help. --Matthew CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.
On Jun 17, 2005, at 1:32 PM, Matt Iskra wrote: > Thanks Mr. Fuhr, I used the psql and it worked. The pgAdmin tool must > have been having trouble with my schema qualified SQL code. The > trigger and trigger function work properly now. Thanks again for the > more generic function - I will have many tables that will be using a > last_touch column and will be using that function. > > Hmmm... I saw the cursor you mention in my screenshot. I cannot > remember if I put it there or if the pgAdmin tool put it there. The > latter would be a nice debugging feature. > > Thanks everybody for all the help. I don't think there is any problem with schema qualified code in pgAdmin3. At least, I tried your function on the Mac version and did not have any problems. Did you by any chance copy and paste text from an example in the documentation using your web browser? I have encountered problems with this before where unexpected non-printing characters are put on the clipboard. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
On Fri, Jun 17, 2005 at 03:05:23PM -0400, John DeSoi wrote: > On Jun 17, 2005, at 1:32 PM, Matt Iskra wrote: > > >Thanks Mr. Fuhr, I used the psql and it worked. The pgAdmin tool must > >have been having trouble with my schema qualified SQL code. The > >trigger and trigger function work properly now. > > I don't think there is any problem with schema qualified code in > pgAdmin3. At least, I tried your function on the Mac version and did > not have any problems. Agreed that that would be strange, especially since creating a schema-qualified table apparently succeeded. Matt, if you retype the code in pgAdmin III, does it work? As John mentions, you might have had a special character in there someplace. We're narrowing down on the problem but let's not draw conclusions prematurely. Also, please don't keep changing the subject header -- that can mess up views of message threads. Threads are usually easier to follow if all the messages have the same subject. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
>>> Michael Fuhr <mike@fuhr.org> 06/17/05 12:34 PM >>> On Fri, Jun 17, 2005 at 03:05:23PM -0400, John DeSoi wrote: > On Jun 17, 2005, at 1:32 PM, Matt Iskra wrote: > > >Thanks Mr. Fuhr, I used the psql and it worked. The pgAdmin tool must > >have been having trouble with my schema qualified SQL code. The > >trigger and trigger function work properly now. > > I don't think there is any problem with schema qualified code in > pgAdmin3. At least, I tried your function on the Mac version and did > not have any problems. Agreed that that would be strange, especially since creating a schema-qualified table apparently succeeded. Matt, if you retype the code in pgAdmin III, does it work? As John mentions, you might have had a special character in there someplace. We're narrowing down on the problem but let's not draw conclusions prematurely. Also, please don't keep changing the subject header -- that can mess up views of message threads. Threads are usually easier to follow if all the messages have the same subject. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ================== Sorry about the subject header. I typed the trigger function exactly like before, I double-checked the code format for hidden code (its ANSI and CR/LF format),opened it with different editor (no weird char), and even put my editor into hex mode - nothing. I ran it using thepgAdmin SQL tool - error right at the function name - the second line in the code below. I run it on the psql commandline and it works. Here is the source code again. CREATE FUNCTION oc.upd_last_touch_test() RETURNS trigger AS $BODY$ BEGIN NEW.last_touch := current_timestamp; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; I am, quite frankly, stumped. However this is not too important as I was able to create the triggers and get the test databaseup and running so the programmers can continue. I just file this sort of fact in my head, along with other databasepeculiarities. Oracle, Informix and SQL Server have plenty of weirdness so I just keep copious notes. --Matthew CONFIDENTIALITY NOTICE: This communication with its contents may contain confidential and/or legally privileged information. It is solely for the use of the intended recipient(s). Unauthorized interception, review, use or disclosure is prohibited and may violate applicable laws including the Electronic Communications Privacy Act. If you are not the intended recipient, please contact the sender and destroy all copies of the communication.
On Mon, 20 Jun 2005 10:15:44 -0700, Matt Iskra wrote > >>> Michael Fuhr <mike@fuhr.org> 06/17/05 12:34 PM >>> > On Fri, Jun 17, 2005 at 03:05:23PM -0400, John DeSoi wrote: > > On Jun 17, 2005, at 1:32 PM, Matt Iskra wrote: > > > > >Thanks Mr. Fuhr, I used the psql and it worked. The pgAdmin tool must > > >have been having trouble with my schema qualified SQL code. The > > >trigger and trigger function work properly now. > > > > I don't think there is any problem with schema qualified code in > > pgAdmin3. At least, I tried your function on the Mac version and did > > not have any problems. > > Agreed that that would be strange, especially since creating a > schema-qualified table apparently succeeded. Matt, if you retype > the code in pgAdmin III, does it work? As John mentions, you might > have had a special character in there someplace. We're narrowing > down on the problem but let's not draw conclusions prematurely. > > Also, please don't keep changing the subject header -- that can > mess up views of message threads. Threads are usually easier to > follow if all the messages have the same subject. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ================== > > Sorry about the subject header. > > I typed the trigger function exactly like before, I double-checked > the code format for hidden code (its ANSI and CR/LF format), opened > it with different editor (no weird char), and even put my editor > into hex mode - nothing. I ran it using the pgAdmin SQL tool - error > right at the function name - the second line in the code below. I > run it on the psql command line and it works. Here is the source > code again. > > CREATE FUNCTION > oc.upd_last_touch_test() > RETURNS trigger AS > $BODY$ > BEGIN > NEW.last_touch := current_timestamp; > RETURN NEW; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > I am, quite frankly, stumped. However this is not too important as I > was able to create the triggers and get the test database up and > running so the programmers can continue. I just file this sort of > fact in my head, along with other database peculiarities. Oracle, > Informix and SQL Server have plenty of weirdness so I just keep > copious notes. > > --Matthew > FWIW I copied the code into pgAdmin3 v1.2.0 on RHEL v3, created a schema called oc hit run and voila. No problem. I have found that pgAdmin will mess up the schema qualified functions in a trigger definition when viewing them. Regards, Keith