Thread: Three questions regarding PL/PGSQL

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!

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

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
informationtechnology      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