Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-interfacespgsql-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

In response to

Responses

pgsql-bugs by date

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

pgsql-interfaces by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group