Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql

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!

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(at)ece(dot)cmu(dot)edu wrote:
> > > >
> > > > The following bug has been logged online:
> > > >
> > > > Bug reference: 2171
> > > > Logged by:
> > > > Email address: andrew5(at)ece(dot)cmu(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)ece(dot)cmu(dot)edu
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)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(at)ece(dot)cmu(dot)edu