Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql - Mailing list pgsql-interfaces

From Bruce Momjian
Subject Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql
Date
Msg-id 200602020448.k124mjG25834@candle.pha.pa.us
Whole thread Raw
Responses Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql  (Michael Meskes <meskes@postgresql.org>)
List pgsql-interfaces
I have researched your report, and you are right, there are two ecpg
bugs here.  First, dollar quoting uses single-quotes internally to do
the quoting, but it does not double any single-quotes in the
dollar-quoted string.

Second, when a dollar quoted string or single-quoted string spans
multiple lines, ecpg does not escape the newline that is part of the
string.  Some compilers will accept an unescaped newline in a string,
while others will not:

    $ gcc -pedantic -c  -g -Wall tst1.c
    tst1.c:5: warning: string constant runs past end of line

It isn't standard so I think we need to replace newline in a string with
"\n\".

Attached is a patch which fixes both of these issues.  This changes ecpg
behavior so I am thinking this patch would only appear in 8.2.

I am unclear if I fixed the \r case properly.

---------------------------------------------------------------------------

andrew5@ece.cmu.edu wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2171
> Logged by:
> Email address:      andrew5@ece.cmu.edu
> PostgreSQL version: 8.1.2
> Operating system:   Linux (Debian)
> Description:        Differences compiling plpgsql in ecpg and psql
> Details:
>
> There appear to be parsing problems with ecpg.  The following example
> program shows code snippets that allow for the successful creation of a
> function (CREATE FUNCTION) only using two different syntaxes: one when
> entered through psql, and another when compiling with ecpg.
>
> The expectation (and hints from the documentation) indicate that the exact
> same method of defining a function should succeed in both cases, but such is
> not the case.
>
> Different quoting and line-wrap behavior is observed between psql and ecpg.
>
> (Thanks for the attention, I hope this is useful!)
>
> BEGIN CODE---------------
> /* This file is bug.pgc. */
> /* Compile as shown:
>    ecpg   bug.pgc -o bug.c
>    gcc -c -g -std=c99 -I/usr/local/pgsql/include -L/usr/local/pgsql/lib
> bug.c -o bug.o
>    gcc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lecpg bug.o -o bug
> */
> /* Run as: ./bug */
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
>
> int main(int argc, char* argv[]) {
>
>   EXEC SQL CONNECT TO DEFAULT;
>
>   EXEC SQL SET AUTOCOMMIT TO ON;
>   EXEC SQL WHENEVER SQLWARNING SQLPRINT;
>   EXEC SQL WHENEVER SQLERROR SQLPRINT;
>
>   EXEC SQL CREATE TABLE My_Table ( Item1 int, Item2 text );
>
>   /* Documentation appears to indicate that only single quotes (') are
>      needed, but this will not ecpg-compile without double-single ('')
>      quotes.  When entered through psql, only the single quotes (')
>      are needed. */
>   /* doc/html/sql-syntax.html#SQL-SYNTAX-DOLLAR-QUOTING: "It is
>      particularly useful when representing string constants inside
>      other constants, as is often needed in procedural function
>      definitions." */
>   /* doc/html/sql-createfunction.html: "Without dollar quoting, any
>      single quotes or backslashes in the function definition must be
>      escaped by doubling them." */
>
>   /* Documentation appears to indicate that the body of the funtion
>      can be extended across multiple lines in the input file (this
>      file) but it will not compile (ecpg) without keeping the function
>      body on one line.  Multiple line input works through psql, but
>      not here.*/
> //bad ecpg,good psql: EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS
> trigger
> //bad ecpg,good psql:   AS $My_Table_Check$
> //bad ecpg,good psql:   BEGIN RAISE NOTICE 'TG_NAME=%, TG WHEN=%', TG_NAME,
> TG_WHEN;
> //bad ecpg,good psql: RETURN NEW;
> //bad ecpg,good psql: END;
> //bad ecpg,good psql: $My_Table_Check$
> //bad ecpg,good psql:   LANGUAGE 'plpgsql';
>   EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS trigger
>     AS $My_Table_Check$ BEGIN RAISE NOTICE ''TG_NAME=%, TG WHEN=%'',
> TG_NAME, TG_WHEN; RETURN NEW; END; $My_Table_Check$
>     LANGUAGE 'plpgsql';
>
>   EXEC SQL CREATE TRIGGER My_Table_Check_Trigger
>     BEFORE INSERT
>     ON My_Table
>     FOR EACH ROW
>     EXECUTE PROCEDURE My_Table_Check();
>
>   EXEC SQL INSERT INTO My_Table VALUES (1234, 'Some random text');
>   EXEC SQL INSERT INTO My_Table VALUES (5678, 'The Quick Brown');
>
>   EXEC SQL DROP TRIGGER My_Table_Check_Trigger ON My_Table;
>   EXEC SQL DROP FUNCTION My_Table_Check();
>   EXEC SQL DROP TABLE My_Table;
>
>   EXEC SQL DISCONNECT ALL;
>
>   return 0;
> }
>
> END CODE------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/interfaces/ecpg/preproc/pgc.l
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/pgc.l,v
retrieving revision 1.140
diff -c -c -r1.140 pgc.l
*** src/interfaces/ecpg/preproc/pgc.l    2 Feb 2006 03:51:41 -0000    1.140
--- src/interfaces/ecpg/preproc/pgc.l    2 Feb 2006 04:38:28 -0000
***************
*** 134,140 ****
   */
  xqstart            {quote}
  xqdouble        {quote}{quote}
! xqinside        [^\\']+
  xqescape        [\\][^0-7]
  xqoctesc        [\\][0-7]{1,3}
  xqhexesc        [\\]x[0-9A-Fa-f]{1,2}
--- 134,140 ----
   */
  xqstart            {quote}
  xqdouble        {quote}{quote}
! xqinside        [^\\'{newline}]
  xqescape        [\\][^0-7]
  xqoctesc        [\\][0-7]{1,3}
  xqhexesc        [\\]x[0-9A-Fa-f]{1,2}
***************
*** 152,158 ****
  dolq_cont        [A-Za-z\200-\377_0-9]
  dolqdelim        \$({dolq_start}{dolq_cont}*)?\$
  dolqfailed        \${dolq_start}{dolq_cont}*
! dolqinside        [^$]+

  /* Double quote
   * Allows embedded spaces and other special characters into identifiers.
--- 152,158 ----
  dolq_cont        [A-Za-z\200-\377_0-9]
  dolqdelim        \$({dolq_start}{dolq_cont}*)?\$
  dolqfailed        \${dolq_start}{dolq_cont}*
! dolqinside        [^$'{newline}]

  /* Double quote
   * Allows embedded spaces and other special characters into identifiers.
***************
*** 161,167 ****
  xdstart            {dquote}
  xdstop            {dquote}
  xddouble        {dquote}{dquote}
! xdinside        [^"]+

  /* special stuff for C strings */
  xdcqq            \\\\
--- 161,167 ----
  xdstart            {dquote}
  xdstop            {dquote}
  xddouble        {dquote}{dquote}
! xdinside        [^"]

  /* special stuff for C strings */
  xdcqq            \\\\
***************
*** 423,429 ****
                  return SCONST;
              }
  <xq>{xqdouble}        { addlitchar('\''); }
! <xq>{xqinside}        { addlit(yytext, yyleng); }
  <xq>{xqescape}      {
                  check_escape_warning();
                  addlit(yytext, yyleng);
--- 423,433 ----
                  return SCONST;
              }
  <xq>{xqdouble}        { addlitchar('\''); }
! <xq>{xqinside}        {
!                 if (yytext[0] == '\r' || yytext[0] == '\n')
!                     addlitchar('\\');    /* C string continuation */
!                 addlitchar(yytext[0]);
!             }
  <xq>{xqescape}      {
                  check_escape_warning();
                  addlit(yytext, yyleng);
***************
*** 473,482 ****
                      yyless(yyleng-1);
                  }
              }
! <xdolq>{dolqinside}     { addlit(yytext, yyleng); }
  <xdolq>{dolqfailed}    { addlit(yytext, yyleng); }
  <xdolq>.    {
!                 /* This is only needed for $ inside the quoted text */
                  addlitchar(yytext[0]);
              }
  <xdolq><<EOF>> { yyerror("unterminated dollar-quoted string"); }
--- 477,493 ----
                      yyless(yyleng-1);
                  }
              }
! <xdolq>{dolqinside}     {
!                 if (yytext[0] == '\r' || yytext[0] == '\n')
!                     addlitchar('\\');    /* C string continuation */
!                 addlitchar(yytext[0]);
!             }
  <xdolq>{dolqfailed}    { addlit(yytext, yyleng); }
  <xdolq>.    {
!                 /* $$ is implemented as a single-quoted string, so double it? */
!                 if (yytext[0] == '\'')
!                     addlitchar(yytext[0]);
!                 /* single quote or dollar sign */
                  addlitchar(yytext[0]);
              }
  <xdolq><<EOF>> { yyerror("unterminated dollar-quoted string"); }

pgsql-interfaces by date:

Previous
From: Judith Retief
Date:
Subject: Re: sql listen fails using libpgtcl
Next
From: Bruce Momjian
Date:
Subject: Re: PQfformat() and Composite Formatted Results