Received: from smtp4.mail.yahoo.com (smtp4.mail.yahoo.com [128.11.69.101]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0G9x4074028 for ; Tue, 16 Jan 2001 04:59:04 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from a5706.pppool.de (HELO jupiter.jw.home) (213.6.87.6) by smtp.mail.vip.suc.yahoo.com with SMTP; 16 Jan 2001 09:57:44 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id FAA02054 for pgsql-sql@postgresql.org; Tue, 16 Jan 2001 05:04:20 -0500 From: Jan Wieck Message-Id: <200101161004.FAA02054@jupiter.jw.home> Subject: Re: Three questions regarding PL/PGSQL To: pgsql-sql@postgresql.org Date: Tue, 16 Jan 2001 05:04:20 -0500 (EST) X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/166 X-Sequence-Number: 827 Josh Berkus wrote: > Folks, > > 1. While I am able to use the %TYPE declaration within > PL/PGSQL functions, I am unable to use this declaration in > the parameters for the function -- I get 'Parse Error at or > near "."' I assume you're trying to do something like CREATE FUNCTION myfunc(mytab.x%TYPE, mytab.y%TYPE) ... because that's the only way I've found to get this error message. Not PL/pgSQL's error here. It's the main parser interpreting the CREATE FUNCTION utility statement that doesn't know how to get it. Indeed, a good idea (for 7.2). Bruce, put it onto TODO please. > 2. When I have a PL/PGSQL function return a custom message > using a VARCHAR return value, I get backslashes in front of > all of the spaces in the message. Can't reproduce that in 7.1(BETA). Could you send a little sql snippet reproducing the behaviour? > 3. Given the odd/weak exception handling within the current > Postgres database engine, has anyone developed strategies to > make certain that their PL/PGSQL functions do not perform > inconsistent updates? If so, can you give some examples? Dunno what's exactly meant by that. Up to now we don't have savepoints and thus, anything done eventually in a PL/pgSQL trigger or function will allways roll back if a transaction get's aborted. Single statements (outside transaction block) have their own transaction, so nothing to worry about. Second you could mean what's been discussed over and over again under subjects like "LOCK arbitrary string" and such, to prevent functions to try things that could produce errors in the first place. Lookup those threads in the archives. Or you could mean to prevent that a trigger, that you expect to UPDATE/DELETE an exact number of rows. Here you could check after the statement in question with GET DIAGNOSTICS (new feature in 7.1) if the correct number of rows has been hit. > Anybody (Jan?) who can shed some light on the above will > receive my enthusiastic gratitude in ASCII text. Some sql examples would allways help. > P.P.S. My most heartfelt gratitude to Jan Wieck for writing > some decent compile error text into the PL/PGSQL compiler, > and to Constantin Teodorescu for putting a terrific function > editor into pgaccess! Getting better compile error messages (anything else than "parse error at or near ...") isn't easy in yacc/bison. Of course, the PL/pgSQL function handler does write some more as DEBUG messages to the Postmaster log. Unfortunately, these don't show up at the frontend side and cannot easily get turned into NOTICE ones because at that time the original ERROR has already been sent to the client and emitting NOTICE's then could confuse the fe/be protocol. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com