Re: Three questions regarding PL/PGSQL

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(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com