PostgreSQL ECPG 开发 DEMO

ECPG 是在C中嵌套SQL的一种用法。
写好pgc文件后，需要使用ecpg程序将pgc编程成C文件来使用。

详细的用法请参考
https://www.postgresql.org/docs/9.5/static/ecpg.html

ecpg的用法, 以EXEC SQL开头表示后面是SQL写法

一些简单的用法
大小写敏感.
.1. 连接数据库

EXEC SQL CONNECT TO target [AS connection-name] [USER user-name];

target : 
dbname[@hostname][:port]
tcp:postgresql://hostname[:port][/dbname][?options]
unix:postgresql://hostname[:port][/dbname][?options]

例子
EXEC SQL CONNECT TO mydb@sql.mydomain.com;

EXEC SQL CONNECT TO unix:postgresql://sql.mydomain.com/mydb AS myconnection USER john;

EXEC SQL BEGIN DECLARE SECTION;
const char *target = "mydb@sql.mydomain.com";
const char *user = "john";
const char *passwd = "secret";
EXEC SQL END DECLARE SECTION;
 ...
EXEC SQL CONNECT TO :target USER :user USING :passwd;
/* or EXEC SQL CONNECT TO :target USER :user/:passwd; */

.2. 定义ecpg变量
PostgreSQL数据类型与ecpg使用的类型的映射关系
https://www.postgresql.org/docs/9.5/static/ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING
有些类型没有一一对应的关系，需要使用ecpg的函数来转换。

下面是一个DEMO
首先需要编写pgc文件。

$ vi t.pgc
#include <stdio.h>
#include <stdlib.h>
#include <pgtypes_numeric.h>

EXEC SQL WHENEVER SQLERROR STOP;

int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
    numeric *num;
    numeric *num2;
    decimal *dec;
EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO tcp:postgresql://xxxcs.com:3433/postgres AS db_digoal USER digoal USING digoal;

    num = PGTYPESnumeric_new();
    dec = PGTYPESdecimal_new();

    EXEC SQL SELECT 12.345::numeric(4,2), 23.456::decimal(4,2) INTO :num, :dec;

    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0));
    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1));
    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2));

    /* Convert decimal to numeric to show a decimal value. */
    num2 = PGTYPESnumeric_new();
    PGTYPESnumeric_from_decimal(dec, num2);

    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0));
    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1));
    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2));

    PGTYPESnumeric_free(num2);
    PGTYPESdecimal_free(dec);
    PGTYPESnumeric_free(num);

    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT ALL;
    return 0;
}

本地环境中需要有依赖的头文件和库.
编译.pgc
-t 表示使用自动提交

ecpg -t -c -I/home/digoal/pgsql9.6/include -o t.c t.pgc

查看编译后的.c

/* Processed by ecpg (4.12.0) */
/* These include files are added by the preprocessor */
#include <ecpglib.h>
#include <ecpgerrno.h>
#include <sqlca.h>
/* End of automatic include section */

#line 1 "t.pgc"
#include <stdio.h>
#include <stdlib.h>
#include <pgtypes_numeric.h>

/* exec sql whenever sqlerror  stop ; */
#line 5 "t.pgc"


int
main(void)
{
/* exec sql begin declare section */
     
     
     

#line 11 "t.pgc"
 numeric * num ;
 
#line 12 "t.pgc"
 numeric * num2 ;
 
#line 13 "t.pgc"
 decimal * dec ;
/* exec sql end declare section */
#line 14 "t.pgc"


    { ECPGconnect(__LINE__, 0, "tcp:postgresql://rdsqm2ffv0wjxnxk5nbsi.pg.rds.aliyuncs.com:3433/postgres" , "digoal" , "digoal" , "db_digoal", 1); 
#line 16 "t.pgc"

if (sqlca.sqlcode < 0) exit (1);}
#line 16 "t.pgc"


    num = PGTYPESnumeric_new();
    dec = PGTYPESdecimal_new();

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select 12.345 :: numeric ( 4 , 2 ) , 23.456 :: decimal ( 4 , 2 )", ECPGt_EOIT, 
        ECPGt_numeric,&(num),(long)1,(long)0,sizeof(numeric), 
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
        ECPGt_decimal,&(dec),(long)1,(long)0,sizeof(decimal), 
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 21 "t.pgc"

if (sqlca.sqlcode < 0) exit (1);}
#line 21 "t.pgc"


    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0));
    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1));
    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2));

    /* Convert decimal to numeric to show a decimal value. */
    num2 = PGTYPESnumeric_new();
    PGTYPESnumeric_from_decimal(dec, num2);

    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0));
    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1));
    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2));

    PGTYPESnumeric_free(num2);
    PGTYPESdecimal_free(dec);
    PGTYPESnumeric_free(num);

    { ECPGtrans(__LINE__, NULL, "commit");
#line 39 "t.pgc"

if (sqlca.sqlcode < 0) exit (1);}
#line 39 "t.pgc"

    { ECPGdisconnect(__LINE__, "ALL");
#line 40 "t.pgc"

if (sqlca.sqlcode < 0) exit (1);}
#line 40 "t.pgc"

    return 0;
}

编译，链接 .c

gcc -I/home/digoal/pgsql9.6/include  -Wall -g  t.c  -L/home/digoal/pgsql9.6/lib -lecpg -lpq -lpgtypes -o t

也可以写成Makefile如下 :
$ vi Makefile
注意需要TAB键.

ECPG = ecpg
CC = gcc

INCLUDES = -I$(shell pg_config --includedir)
LIBPATH = -L$(shell pg_config --libdir)
CFLAGS += $(INCLUDES)
LDFLAGS += -Wall -g
LDLIBS += $(LIBPATH) -lecpg -lpq -lpgtypes

%.c: %.pgc
        $(ECPG) -t -c $(INCLUDES) -o $@ $<

%: %.o
        $(CC) $(CFLAGS) $(LDFLAGS) $(LDLIBS) -o $@ $<

TESTS = t t.c

default: $(TESTS)

clean:
        rm -f *.o *.so t t.c

使用时注意把pg_config弄进PATH

$ export PATH=/home/digoal/pgsql9.6/bin:$PATH
$ make

使用编译好的t :

./t 
numeric = 12
numeric = 12.4
numeric = 12.35
decimal = 23
decimal = 23.5
decimal = 23.46