pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Three questions regarding PL/PGSQL 3+ messages / 2 participants [nested] [flat]
* Three questions regarding PL/PGSQL @ 2001-01-16 02:08 Josh Berkus <josh@agliodbs.com> 0 siblings, 0 replies; 3+ messages in thread From: Josh Berkus @ 2001-01-16 02:08 UTC (permalink / raw) To: pgsql-sql 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 "."' 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. 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? Anybody (Jan?) who can shed some light on the above will receive my enthusiastic gratitude in ASCII text. -Josh Berkus P.S. I'm using Postgres 7.0.2 on SuSE 7.0 and use pgaccess extensively for function editing. 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! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Three questions regarding PL/PGSQL @ 2001-01-16 10:04 Jan Wieck <janwieck@Yahoo.com> 2001-01-16 21:18 ` Re: Three questions regarding PL/PGSQL Josh Berkus <josh@agliodbs.com> 0 siblings, 1 reply; 3+ messages in thread From: Jan Wieck @ 2001-01-16 10:04 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Three questions regarding PL/PGSQL 2001-01-16 10:04 Re: Three questions regarding PL/PGSQL Jan Wieck <janwieck@Yahoo.com> @ 2001-01-16 21:18 ` Josh Berkus <josh@agliodbs.com> 0 siblings, 0 replies; 3+ messages in thread From: Josh Berkus @ 2001-01-16 21:18 UTC (permalink / raw) To: pgsql-sql Jan, > 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 That's correct. > Indeed, a good idea (for 7.2). Bruce, put it onto TODO > please. Thanks! I'm a little surprised that this hasn't come up before -- after all, why did you include PLSQL-style %TYPE and %ROWTYPE declarations if not for parameters? > Can't reproduce that in 7.1(BETA). Could you send a little > sql snippet reproducing the behaviour? Sure, when I get home. I've a feeling that it's related to the Function handler in 7.0.x storing functions as TEXT. What I'm trying to make sure of is that it's not related to using PGAccess. > 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. What I'm talking about is how, if an error occurs, the entire function rolls back, not just a selected portion. I can't even include a BEGIN TRANSACTION statment in a function; it errors out on compile. Nor can I return a custom error message in place of a database error. ALso, in other database engines, I've been able to use transactions to prevent the interleaving of conflicting updates on the database server. For example, I have some functions that insert a row into a table and then report back the ID of the new row: INSERT INTO clients ( ... ) VALUES ( ... ); SELECT CURRVAL(client_id) INTO new_client; It's vitally important that another operation on the clients table does not execute between the INSERT and the SELECT CURRVAL. It may be that by creating transactions by default PGSQL functions are alredy doing this; some reassurance on that count would be nice. > Some sql examples would allways help. More later when I get back to my PGSQL server. > 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. Hey, just the fact that you spit back "Error on Line 38" cuts my debugging time in half over the SQL handler's "Error at or near ';'" Of course, running a tail on the postmaster log helps, too ... -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-01-16 21:18 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-01-16 02:08 Three questions regarding PL/PGSQL Josh Berkus <josh@agliodbs.com> 2001-01-16 10:04 Re: Three questions regarding PL/PGSQL Jan Wieck <janwieck@Yahoo.com> 2001-01-16 21:18 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox