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

Question

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.

Is update_some_logic a function or a procedure ? If this a function, what is the return type ? — pifor
– pifor, Commented Jun 10, 2020 at 13:03
@pifor it's a PG function (we ported Oracle stored procedures to functions), return type void — Francisco Hernandez
– Francisco Hernandez, Commented Jun 10, 2020 at 15:02

pifor · Accepted Answer · 2020-06-10 15:55:32Z

3

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;

answered Jun 10, 2020 at 15:55

pifor

7,9722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcelo Diaz · Accepted Answer · 2020-07-15 23:18:27Z

0

You'll need work with share variables for those parameters.

EXEC SQL BEGIN DECLARE SECTION;

double parameter_1;
double parameter_2; 
char parameter_3[100];
char parameter_4[100];
int result; //I guess your function return int??

EXEC SQL END DECLARE SECTION;


EXEC SQL SELECT update_some_logic(:parameter_1, :parameter_2, :parameter_3, :parameter_4) INTO :result;

if (sqlca.sqlcode!=0)
{
  printf("Error: %ld\n", sqlca.sqlcode);
  printf("Message:%s\n", sqlca.sqlerrm.sqlerrmc);
}

printf("It works %d\n", result);

answered Jul 15, 2020 at 23:18

Marcelo Diaz

212 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related