Re: Three questions regarding PL/PGSQL - Mailing list 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
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