PostgreSQL ECPG ifdef include等预处理用法

PostgreSQL 社区版本的ecpg在一些预处理的用法上和Oracle的PROC有一些不一样的地方，使用者需要注意。
例如社区版本的ecpg不支持c里面使用的#ifdef或者#ifndef这样的预处理语法，需要用其他写法来替代。
所以你如果使用#ifdef这样的写法在.pgc里面，在使用ecpg编译时报错，你可能觉得很奇怪。
例子

$ vi t.pgc

#include stdio.h 

#include stdlib.h 

#include pgtypes_numeric.h 

EXEC SQL WHENEVER SQLERROR STOP;

main(void)

EXEC SQL BEGIN DECLARE SECTION;

 numeric *num;

 numeric *num2;

 decimal *dec;

#ifdef ABC // ecpg对于这样的代码会整段拷贝输出到.c，同时里面的所有语句都需要被parser过一遍，因此可能报错

 errtype *abc;

#endif

EXEC SQL END DECLARE SECTION;

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

 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;

}

编译报错

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

t.pgc:16: ERROR: unrecognized data type name "errtype"

如何修正呢？

#ifdef ABC

 errtype *abc;

#endif

改成

EXEC SQL ifdef ABC;

 errtype *abc;

EXEC SQL endif;

编译通过

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

加上ABC宏，里面的这一段才会过parser。

digoal@iZ25zysa2jmZ- ecpg -t -c -I/home/digoal/pgsql9.6/include -o t.c -D ABC t.pgc

t.pgc:16: ERROR: unrecognized data type name "errtype"

PostgreSQL ecpg还支持include的预处理.
如果include的头文件中包含了ECPG的用法，必须使用以下几种方式来预处理

EXEC SQL INCLUDE filename;

EXEC SQL INCLUDE filename 

EXEC SQL INCLUDE "filename";

如果include的文件没有ECPG的语法，则不需要这么做，使用原来的方法即可，ecpg会直接拷贝输出到.c

#include filename.h 

对于EDB提供的ecpg版本，是支持#ifdef这种写法的，用于兼容ORACLE的PROC。

参考
https://www.postgresql.org/docs/9.5/static/ecpg-preproc.html

EDB ECPG
https://www.enterprisedb.com/docs/en/9.5/ecpg/Postgres_Plus_Advanced_Server_ecpgPlus_Guide.1.24.html#

The ECPGPlus C-preprocessor enforces two behaviors that are dependent on the mode in which you invoke ECPGPlus:

PROC mode

non-PROC mode

Compiling in PROC mode

In PROC mode, ECPGPlus allows you to:

Declare host variables outside of an EXEC SQL BEGIN/END DECLARE SECTION.

Use any C variable as a host variable as long as it is of a data type compatible with ECPG.

When you invoke ECPGPlus in PROC mode (by including the -C PROC keywords), the ECPG compiler honors the following C-preprocessor directives:

#include

#if expression

#ifdef symbolName

#ifndef symbolName

#else

#elif expression

#endif

#define symbolName expansion

#define symbolName([macro arguments]) expansion

#undef symbolName

#defined(symbolName)

Pre-processor directives are used to effect or direct the code that is received by the compiler. For example, using the following code sample:

#if HAVE_LONG_LONG == 1

#define BALANCE_TYPE long long

#else

#define BALANCE_TYPE double

#endif

BALANCE_TYPE customerBalance;

If you invoke ECPGPlus with the following command-line arguments:

ecpg –C PROC –DHAVE_LONG_LONG=1

ECPGPlus will copy the entire fragment (without change) to the output file, but will only send the following tokens to the ECPG parser:

long long customerBalance;

On the other hand, if you invoke ECPGPlus with the following command-line arguments:

ecpg –C PROC –DHAVE_LONG_LONG=0

The ECPG parser will receive the following tokens:

double customerBalance;

If your code uses preprocessor directives to filter the code that is sent to the compiler, the complete code is retained in the original code, while the ECPG parser sees only the processed token stream.

Compiling in non-PROC mode

If you do not include the -C PROC command-line option:

C preprocessor directives are copied to the output file without change.

You must declare the type and name of each C variable that you intend to use as a host variable within an EXEC SQL BEGIN/END DECLARE section.

When invoked in non-PROC mode, ECPG implements the behavior described in the PostgreSQL Core documentation

使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} )
Postgresql pg_dump&pg_restore用法 PostgreSQL提供的一个工具pg_dump,逻辑导出数据，生成sql文件或其他格式文件，pg_dump是一个客户端工具，可以远程或本地导出逻辑数据，恢复数据至导出时间点。pg_dump 一次只转储一个数据库, 并且不会转储有关角色或表空间的信息 (因为那些是群集范围而不是每个数据库)。
PostgreSQL 窗口函数的用法 PostgreSQL之窗口函数的用法 转载请注明出处:https://www.cnblogs.com/funnyzpc/p/9311281.html PostgreSQL的高级特性本准备三篇的(递归、窗口函数、JSON)，结果中间一直一直加班 和遗忘 拖到现在才写到中篇，欸，加班真不是一件好事情。