So how can emedded SQL in C to get the record type which returning from plpgsql function?
I have tested as following code:
CREATE OR REPLACE FUNCTION test4(IN a integer, OUT b int,OUT c int)
AS
$BODY$
Declare
begin b:=100;
c:=200;
return;
END $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
// imbedded SQL in C
// test4.gpc
#include <postgres.h>
#include <funcapi.h>
int db_inner_connect()
{
int DBFlag=-1;
EXEC SQL WHENEVER sqlerror SQLPRINT;
//George modify
EXEC SQL CONNECT to test USER test IDENTIFIED BY test;
DBFlag = 0 ; //0
return DBFlag;
SQL_ERROR:
DBFlag = -1 ; //-1
return DBFlag;
}
int db_inner_disconnect()
{
EXEC SQL DISCONNECT;
return -1;
}
int test(int a)
{
EXEC SQL TYPE my_type IS STRUCT
{
int *a;
int *b;
};
EXEC SQL BEGIN DECLARE SECTION;
my_type ppp;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER sqlerror SQLPRINT;
EXEC SQL select test4(1) into :ppp;
EXEC SQL COMMIT;
return 0;
}
//main functuin
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <pgtypes_numeric.h>
#include <postgres.h>
#include <funcapi.h>
static void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
/*************** Main ****************/
/*test4_main.c*/
int main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields;
int i,
j;
int v_test;
int aa=354571;
//int test(int a);
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = amri";
v_test=db_inner_connect();
v_test=test(aa);
v_test=db_inner_disconnect();
return 0;
}
/*compile pass*/
ecpg -c test4.gpc -I/u1/PostgreSQL/8.4/include
cc -g -o test4 test4_main.c test4.c -I/u1/PostgreSQL/8.4/include/ -I/u1/PostgreSQL/8.4/include/postgresql/server/ -lpgport -lz -lreadline -ltermcap -lcrypt -lxml2 -lresolv -lnsl -ldl -lm -lbsd -lecpg -lpq -L/u1/PostgreSQL/8.4/lib/ -lpgtypes
/*running binary file, cause error*/
./test4
SQL error: invalid input syntax for type int: "(100,200)", on line 37
My project is migrating from Oracle to postgresql,can you help me ?
Thanks.
Regards,
George
------------------------------------------------------------
Duozhong Sun
Guangdong Revenco Enterprise Co. Ltd. AMRI Department
Tel：+86 20 8713 5305,+86 15800005957
Fax：+86 20 87135388
Address：368 Guangzhou Avenue South,Guangzhou 510300,P.R.C.
------------------------------------------------------------
