BUG #2171: Differences compiling plpgsql in ecpg and psql - Mailing list pgsql-bugs

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