Search code examples
cpostgresqlecpg

How to call PostgreSQL function from C code (ECPG)?

We are porting Oracle Pro*C code to PostgreSQL ECPG. We had several Oracle stored procedures that were ported into PostgreSQL functions like:

db1.update_some_logic(double precision, double precision, text, text)

On the C file I've tried several things but nothing seems to work.

Option 1:

EXEC SQL SELECT db1.update_some_logic(10411, 920, 'TYT','N');

Error 1:

too few arguments on line 4379

Option 2: 

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt2 = "SELECT db1.update_some_logic(10411, 920, 'TYT','N');";
EXEC SQL END DECLARE SECTION;
EXEC SQL EXECUTE IMMEDIATE :stmt2;

Error 2:

too few arguments on line 4384

The function clearly has 4 arguments, not sure what I am missing here.

Solution

  • I can reproduce the issue with PostgreSQL 12.3.

    I have found following workaround:

    • the function should not return void but a value for example an integer
    • the ECPG program must execute the function and get the returned value with SELECT ... INTO:

    EXEC SQL SELECT update_some_logic(10411, 920, 'TYT','N') INTO :v_key;