pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: sqllist <pgsql-sql@postgresql.org>
Subject: Re: Three questions regarding PL/PGSQL
Date: Tue, 16 Jan 2001 13:18:42 -0800
Message-ID: <3A64BAB2.69FD4ECD@agliodbs.com> (raw)
References: <200101161004.FAA02054@jupiter.jw.home>

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



view thread (3+ messages)

Message-ID: <3A64BAB2.69FD4ECD@agliodbs.com>
Permalink:  ../3A64BAB2.69FD4ECD@agliodbs.com/
Also on:    postgresql.org/message-id/3A64BAB2.69FD4ECD@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com
  Subject: Re: Three questions regarding PL/PGSQL
  In-Reply-To: <3A64BAB2.69FD4ECD@agliodbs.com>
  References: <200101161004.FAA02054@jupiter.jw.home> <3A64BAB2.69FD4ECD@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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