Postgres Pro
Downloads
Home   >   mailing lists

Thread: RAISE concatination/variables in plpgsql

RAISE concatination/variables in plpgsql

From
"Henshall, Stuart - WCP"
Date:
SEVERITY:Minor Anoyance
In the plpgsql docs it has the following example:
RAISE NOTICE ''Id number '' || key || '' not found!'';
When I put a function round this statement it gives a compile error at the
|.
Also when fiddling if I put a variable first it complains about that
variable (eg key || '' val.....'')
Here is the script I  ran:
DROP FUNCTION tstktxt(text);
CREATE FUNCTION tstktxt(text) RETURNS text AS '
DECLARE
    key ALIAS FOR $1;
BEGIN
    RAISE NOTICE ''Id number '' || key || '' not found!'';
    RETURN key;
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION tstkint(int4);
CREATE FUNCTION tstkint(int4) RETURNS int4 AS '
DECLARE
    key ALIAS FOR $1;
BEGIN
    RAISE NOTICE ''Id number '' || key || '' not found!'';
    RETURN key;
END;
' LANGUAGE 'plpgsql';

SELECT tstktxt('Test');
SELECT tstkint(42);

This gave the following result:

DROP
CREATE
DROP
CREATE
psql:core/kytst.sql:21: NOTICE:  plpgsql: ERROR during compile of tstktxt
near line 4
psql:core/kytst.sql:21: ERROR:  parse error at or near "|"
psql:core/kytst.sql:22: NOTICE:  plpgsql: ERROR during compile of tstkint
near line 4
psql:core/kytst.sql:22: ERROR:  parse error at or near "|"

Is this a bug or documentation error?
I'm running on cygwin 1.1.7, cygipc 1.9.2, on win98SE
Here's the postmaster output (with -d2):
 <<z>>
Attachment

Re: RAISE concatination/variables in plpgsql

From
Tom Lane
Date:
"Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk> writes:
> In the plpgsql docs it has the following example:
> RAISE NOTICE ''Id number '' || key || '' not found!'';
> When I put a function round this statement it gives a compile error at the
> |.
> Also when fiddling if I put a variable first it complains about that
> variable (eg key || '' val.....'')

Looking at the plpgsql code, it's clear that what's actually implemented
is
    RAISE level string-literal [ , variable [ , variable [ ... ] ]
which is pretty bletcherous; seems like it should accept a list of
expressions instead.  But for 7.1, I guess this is a documentation bug
rather than something to change in the code.

            regards, tom lane

Re: RAISE concatination/variables in plpgsql

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Looking at the plpgsql code, it's clear that what's actually implemented
>> is
>> RAISE level string-literal [ , variable [ , variable [ ... ] ]

> I see the current docs showing:

>     RAISE level 'format' [, identifier [...]];

> Is this acceptable?  Should 'identifier' be 'variable'?

Probably.  And 'format' is even more misleading, since it implies that
you write a printf-like format string, which you do not.  The output is
just the concatenation of the literal and the variable values.

>> which is pretty bletcherous; seems like it should accept a list of
>> expressions instead.  But for 7.1, I guess this is a documentation bug
>> rather than something to change in the code.

> Do I need a TODO item here?

It seems in need of fixing to me ...

            regards, tom lane

Re: RAISE concatination/variables in plpgsql

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Looking at the plpgsql code, it's clear that what's actually implemented
> >> is
> >> RAISE level string-literal [ , variable [ , variable [ ... ] ]
>
> > I see the current docs showing:
>
> >     RAISE level 'format' [, identifier [...]];
>
> > Is this acceptable?  Should 'identifier' be 'variable'?
>
> Probably.  And 'format' is even more misleading, since it implies that
> you write a printf-like format string, which you do not.  The output is
> just the concatenation of the literal and the variable values.

Updated with patch attached.

>
> >> which is pretty bletcherous; seems like it should accept a list of
> >> expressions instead.  But for 7.1, I guess this is a documentation bug
> >> rather than something to change in the code.
>
> > Do I need a TODO item here?
>
> It seems in need of fixing to me ...

TODO updated with:

    * Allow Pl/PgSQL's RAISE function to take expressions

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/plsql.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/plsql.sgml,v
retrieving revision 2.25
diff -c -r2.25 plsql.sgml
*** doc/src/sgml/plsql.sgml    2001/03/23 22:07:50    2.25
--- doc/src/sgml/plsql.sgml    2001/05/08 00:05:24
***************
*** 1306,1312 ****
      <productname>Postgres</productname> elog mechanism.

  <synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>'
<optional>,<replaceable class="parameter">identifier</replaceable> <optional>...</optional></optional>; 
  </synopsis>

      Inside the format, <literal>%</literal> is used as a placeholder for the
--- 1306,1312 ----
      <productname>Postgres</productname> elog mechanism.

  <synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">string</replaceable>'
<optional>,<replaceable class="parameter">variable</replaceable> <optional>...</optional></optional>; 
  </synopsis>

      Inside the format, <literal>%</literal> is used as a placeholder for the

Re: RAISE concatination/variables in plpgsql

From
Tom Lane
Date:
I wrote:
>> Probably.  And 'format' is even more misleading, since it implies that
>> you write a printf-like format string, which you do not.  The output is
>> just the concatenation of the literal and the variable values.

Ugh.  Should've read the code before pontificating ;-).  The code makes
clear what is quite unclear in the docs:
        /*
         * Occurences of a single % are replaced by the next arguments
         * external representation. Double %'s are left as is so elog()
         * will also don't touch them.
         */
So "format" is appropriate, but the next sentence could use some
improvement.

            regards, tom lane

Re: RAISE concatination/variables in plpgsql

From
Bruce Momjian
Date:
> "Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk> writes:
> > In the plpgsql docs it has the following example:
> > RAISE NOTICE ''Id number '' || key || '' not found!'';
> > When I put a function round this statement it gives a compile error at the
> > |.
> > Also when fiddling if I put a variable first it complains about that
> > variable (eg key || '' val.....'')
>
> Looking at the plpgsql code, it's clear that what's actually implemented
> is
>     RAISE level string-literal [ , variable [ , variable [ ... ] ]

I see the current docs showing:

    RAISE level 'format' [, identifier [...]];

Is this acceptable?  Should 'identifier' be 'variable'?

> which is pretty bletcherous; seems like it should accept a list of
> expressions instead.  But for 7.1, I guess this is a documentation bug
> rather than something to change in the code.

Do I need a TODO item here?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: RAISE concatination/variables in plpgsql

From
Bruce Momjian
Date:
OK, SGML updated.

> I wrote:
> >> Probably.  And 'format' is even more misleading, since it implies that
> >> you write a printf-like format string, which you do not.  The output is
> >> just the concatenation of the literal and the variable values.
>
> Ugh.  Should've read the code before pontificating ;-).  The code makes
> clear what is quite unclear in the docs:
>         /*
>          * Occurences of a single % are replaced by the next arguments
>          * external representation. Double %'s are left as is so elog()
>          * will also don't touch them.
>          */
> So "format" is appropriate, but the next sentence could use some
> improvement.
>
>             regards, tom lane
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/plsql.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/plsql.sgml,v
retrieving revision 2.26
diff -c -r2.26 plsql.sgml
*** doc/src/sgml/plsql.sgml    2001/05/08 00:09:22    2.26
--- doc/src/sgml/plsql.sgml    2001/05/08 00:25:57
***************
*** 1306,1319 ****
      <productname>Postgres</productname> elog mechanism.

  <synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">string</replaceable>'
<optional>,<replaceable class="parameter">variable</replaceable> <optional>...</optional></optional>; 
  </synopsis>

!     Inside the format, <literal>%</literal> is used as a placeholder for the
!     subsequent comma-separated identifiers. Possible levels are
!     DEBUG (silently suppressed in production running databases), NOTICE
!     (written into the database log and forwarded to the client application)
!     and EXCEPTION (written into the database log and aborting the transaction).
     </para>

     <para>
--- 1306,1320 ----
      <productname>Postgres</productname> elog mechanism.

  <synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>'
<optional>,<replaceable class="parameter">variable</replaceable> <optional>...</optional></optional>; 
  </synopsis>

!     Inside the format, <literal>%</literal> is replaced by the next argument's
!     external representation. Double %'s are left unchanged for internal
!     reasons.  Possible levels are DEBUG (silently suppressed in production
!     running databases), NOTICE (written into the database log and forwarded to
!     the client application) and EXCEPTION (written into the database log and
!     aborting the transaction).
     </para>

     <para>