Re: Three questions regarding PL/PGSQL - Mailing list 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
transactionblock)   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"
andsuch,   to prevent functions to try things that could produce  errors   in the first place.  Lookup those threads in
thearchives.
 
   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
ofrows  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
becauseat  that  time  the  original   ERROR  has  already  been  sent  to  the  client and emitting   NOTICE's then
couldconfuse 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