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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: andrew5(at)ece(dot)cmu(dot)edu
Cc: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>, PostgreSQL-interfaces <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: [BUGS] BUG #2171: Differences compiling plpgsql in ecpg and psql
Date: 2006-02-02 04:48:45
Message-ID: 200602020448.k124mjG25834@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-interfaces pgsql-patches


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

Attachment Content-Type Size
unknown_filename text/plain 3.0 KB

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Grossman 2006-02-02 08:27:52 BUG #2232: Function (store procedure) prublem
Previous Message Michael Grossman 2006-02-01 20:29:05 PostgreSQL Windows

Browse pgsql-interfaces by date

  From Date Subject
Next Message Andrew Dunstan 2006-02-02 18:36:41 Re: [BUGS] BUG #2171: Differences compiling plpgsql in
Previous Message Judith Retief 2006-02-02 04:17:59 Re: sql listen fails using libpgtcl

Browse pgsql-patches by date

  From Date Subject
Next Message ITAGAKI Takahiro 2006-02-02 08:19:43 Re: TODO-Item: B-tree fillfactor control
Previous Message Tom Lane 2006-02-02 03:08:18 Re: pg_restore COPY error handling