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

Andrew Klosterman wrote:
>
> As to my opinion on affecting any work-arounds:  I got around this issue
> by not line wrapping my long strings in "EXEC SQL ..." statements that
> ecpg processes.
>
> I would edit the plpgsql function that I was writing in an external
> editor, make sure it installed by copy&paste into the psql window, and
> then doctoring the quotes and removing the line breaks so that ecpg would
> accept the function and the compiled C code would install it.  This made
> for some crufty looking code in my editor, but it worked.
>
> If the patched code takes care of eating line breaks then it shouldn't
> make any difference to my all-on-one-line solution.  Not yet having tried
> the patched code, I can only assume that I could now "beautify" my code by
> inserting line breaks and do away with the external editor and copy&paste
> procedures.  :-)
>
> If anyone has heard of other work-arounds for the situation, I'm happy to
> hear about them and offer an opinion!

Yep, that's about right.  The new code is not going to auto-wrap your
existing function, but it will preserve line wraps you use in ecpg
SQl code.

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

>
> On Fri, 3 Feb 2006, Bruce Momjian wrote:
> <snip>
> > I am going to apply this to HEAD.  It is a fix, but I am worried it
> > might affect ecpg users who might have worked-around these problems and
> > this might break their work-around.  I am looking for opinions on
> > applying this to back releases.
> >
> > ---------------------------------------------------------------------------
> >
> > Andrew Klosterman wrote:
> > > On Wed, 1 Feb 2006, Bruce Momjian wrote:
> > >
> > > >
> > > > 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
> > > >
> > >
> > >
> > > --Andrew J. Klosterman
> > > andrew5@ece.cmu.edu
> > >
> >
> > --
> >   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
> >
>
>
> --Andrew J. Klosterman
> andrew5@ece.cmu.edu

Content-Description:

> Index: src/interfaces/ecpg/preproc/output.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/output.c,v
> retrieving revision 1.12
> diff -c -c -r1.12 output.c
> *** src/interfaces/ecpg/preproc/output.c    13 Oct 2004 01:25:13 -0000    1.12
> --- src/interfaces/ecpg/preproc/output.c    3 Feb 2006 05:33:38 -0000
> ***************
> *** 2,7 ****
> --- 2,9 ----
>
>   #include "extern.h"
>
> + static void ouput_escaped_str(char *cmd);
> +
>   void
>   output_line_number(void)
>   {
> ***************
> *** 10,30 ****
>   }
>
>   void
> ! output_simple_statement(char *cmd)
>   {
> !     int            i,
> !                 j = strlen(cmd);;
> !
> !     /* output this char by char as we have to filter '\"' */
> !     for (i = 0; i < j; i++)
> !     {
> !         if (cmd[i] != '"')
> !             fputc(cmd[i], yyout);
> !         else
> !             fputs("\\\"", yyout);
> !     }
>       output_line_number();
> !     free(cmd);
>   }
>
>   /*
> --- 12,22 ----
>   }
>
>   void
> ! output_simple_statement(char *stmt)
>   {
> !     ouput_escaped_str(stmt);
>       output_line_number();
> !     free(stmt);
>   }
>
>   /*
> ***************
> *** 106,125 ****
>   void
>   output_statement(char *stmt, int mode, char *con)
>   {
> -     int            i,
> -                 j = strlen(stmt);
> -
>       fprintf(yyout, "{ ECPGdo(__LINE__, %d, %d, %s, \"", compat, force_indicator, con ? con : "NULL");
> !
> !     /* output this char by char as we have to filter '\"' */
> !     for (i = 0; i < j; i++)
> !     {
> !         if (stmt[i] != '"')
> !             fputc(stmt[i], yyout);
> !         else
> !             fputs("\\\"", yyout);
> !     }
> !
>       fputs("\", ", yyout);
>
>       /* dump variables to C file */
> --- 98,105 ----
>   void
>   output_statement(char *stmt, int mode, char *con)
>   {
>       fprintf(yyout, "{ ECPGdo(__LINE__, %d, %d, %s, \"", compat, force_indicator, con ? con : "NULL");
> !     ouput_escaped_str(stmt);
>       fputs("\", ", yyout);
>
>       /* dump variables to C file */
> ***************
> *** 135,137 ****
> --- 115,135 ----
>       if (connection != NULL)
>           free(connection);
>   }
> +
> +
> + static void
> + ouput_escaped_str(char *str)
> + {
> +     int            i, len = strlen(str);
> +
> +     /* output this char by char as we have to filter " and \n */
> +     for (i = 0; i < len; i++)
> +     {
> +         if (str[i] == '"')
> +             fputs("\\\"", yyout);
> +         else if (str[i] == '\n')
> +             fputs("\\n\\\n", yyout);
> +         else
> +             fputc(str[i], yyout);
> +     }
> + }
> 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    3 Feb 2006 05:33:38 -0000
> ***************
> *** 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        [^$']+
>
>   /* Double quote
>    * Allows embedded spaces and other special characters into identifiers.
> ***************
> *** 476,482 ****
>   <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"); }
> --- 476,485 ----
>   <xdolq>{dolqinside}     { addlit(yytext, yyleng); }
>   <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"); }

--
  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