pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <janwieck@Yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Three questions regarding PL/PGSQL
Date: Tue, 16 Jan 2001 05:04:20 -0500 (EST)
Message-ID: <200101161004.FAA02054@jupiter.jw.home> (raw)

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




view thread (3+ messages)  latest in thread

Message-ID: <200101161004.FAA02054@jupiter.jw.home>
Permalink:  ../200101161004.FAA02054@jupiter.jw.home/
Also on:    postgresql.org/message-id/200101161004.FAA02054@jupiter.jw.home

 · 

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: janwieck@Yahoo.com
  Subject: Re: Three questions regarding PL/PGSQL
  In-Reply-To: <200101161004.FAA02054@jupiter.jw.home>
  References: <200101161004.FAA02054@jupiter.jw.home>

* 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