pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Three questions regarding PL/PGSQL
3+ messages / 2 participants
[nested] [flat]

* Three questions regarding PL/PGSQL
@ 2001-01-16 02:08 Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2001-01-16 02:08 UTC (permalink / raw)
  To: pgsql-sql

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!



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Three questions regarding PL/PGSQL
@ 2001-01-16 10:04 Jan Wieck <janwieck@Yahoo.com>
  2001-01-16 21:18 ` Re: Three questions regarding PL/PGSQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Jan Wieck @ 2001-01-16 10:04 UTC (permalink / raw)
  To: 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 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@Yahoo.com #


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Three questions regarding PL/PGSQL
  2001-01-16 10:04 Re: Three questions regarding PL/PGSQL Jan Wieck <janwieck@Yahoo.com>
@ 2001-01-16 21:18 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2001-01-16 21:18 UTC (permalink / raw)
  To: 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 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-01-16 21:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-16 02:08 Three questions regarding PL/PGSQL Josh Berkus <josh@agliodbs.com>
2001-01-16 10:04 Re: Three questions regarding PL/PGSQL Jan Wieck <janwieck@Yahoo.com>
2001-01-16 21:18 ` Josh Berkus <josh@agliodbs.com>

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