Consume SOAP Web Service from PostgreSQL User Defined Function in C
- I download gSOAP on my Ubuntu Laptop. Build and install it into /opt/gsoap directory.
- Use gSOAP wsdl2h to obtain the gSOAP header file cy.h specification of the web service’s WSDL document:
/opt/gsoap/bin/wsdl2h -c -o cy.h http://www.currencyserver.de/webservice/currencyserverwebservice.asmx?WSDL
- Generate a bunch of source, header, xml and nsmap files based on cy.h header file:
/opt/gsoap/bin/soapcpp2 -1 -c -C -pcy cy.h
- Copy stdsoap2.c from gSOAP source code distribution into current work directory.
- Create C source code cy.c for PostgreSQL User Defined Functions with Version 1 Calling Conventions:
#include <postgres.h>
#include <fmgr.h>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif#include <utils/builtins.h>
#include <funcapi.h>
#include “cyH.h”
#include “CurrencyServerWebServiceSoap.nsmap”PG_FUNCTION_INFO_V1(cy);
Datum cy(PG_FUNCTION_ARGS);void errExit(struct soap* soap);
void cleanExit(struct soap* soap);
struct cyData{ struct soap* soap; char** provList; };Datum cy(PG_FUNCTION_ARGS) {
struct FuncCallContext* funcctx;
TupleDesc tupdesc;if (SRF_IS_FIRSTCALL()) {
MemoryContext oldcontext;
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
if(get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) {ereport(ERROR,( errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(“function returning record called in context “
“that cannot accept type record”)
));
}
funcctx->tuple_desc=BlessTupleDesc(tupdesc);
if(PG_NARGS()!=2)elog(ERROR, “argument count must be %d”, 2);struct cyData* pData=(struct cyData*)palloc(sizeof(struct cyData));
pData->soap=soap_new();
struct _ns1__getProviderList reqProvList;
struct _ns1__getProviderListResponse respProvList;
soap_default__ns1__getProviderList(pData->soap, &reqProvList);
soap_default__ns1__getProviderListResponse(pData->soap, &respProvList);
if(soap_call___ns2__getProviderList(pData->soap, NULL, NULL, &reqProvList, &respProvList)){errExit(pData->soap);}
char* delim=”, “;
char* ch=strtok(respProvList.getProviderListResult,delim);
int count=0;
while(ch){pData->provList= count==0 ? (char**)palloc(++count*sizeof(char*))
: (char**)repalloc(pData->provList, ++count*sizeof(char*));
pData->provList[count-1]=pstrdup(ch);
ch=strtok(NULL,delim);}
funcctx->max_calls=count;
funcctx->user_fctx=(void*)pData;MemoryContextSwitchTo(oldcontext);
}
funcctx = SRF_PERCALL_SETUP();
if (funcctx->call_cntr max_calls) {
Datum result;
HeapTuple tuple;
bool isnull[]={false,false,false,false};struct cyData* pData=(struct cyData*)funcctx->user_fctx;
struct _ns1__getCurrencyValue req;
struct _ns1__getCurrencyValueResponse resp;
soap_default__ns1__getCurrencyValue(pData->soap, &req);req.provider=pData->provList[funcctx->call_cntr];
req.srcCurrency=TextDatumGetCString(PG_GETARG_DATUM(0));
req.dstCurrency=TextDatumGetCString(PG_GETARG_DATUM(1));
if(soap_call___ns2__getCurrencyValue(pData->soap, NULL, NULL, &req, &resp)){errExit(pData->soap);}
Datum values[]={DirectFunctionCall1(textin, CStringGetDatum(pData->provList[funcctx->call_cntr])),
PG_GETARG_DATUM(0),
PG_GETARG_DATUM(1),
DirectFunctionCall1(float8_numeric, Float8GetDatum(resp.getCurrencyValueResult))};
tuple=heap_form_tuple(funcctx->tuple_desc, values, isnull);
result = HeapTupleGetDatum(tuple);SRF_RETURN_NEXT(funcctx, result);
} else {
struct cyData* pData=(struct cyData*)funcctx->user_fctx;
cleanExit(pData->soap);
SRF_RETURN_DONE(funcctx);}
}
void errExit(struct soap* soap) {
char buf[201]=”";
soap_sprint_fault(soap, buf, 200);
cleanExit(soap);
elog(ERROR, “%s”, buf);}
void cleanExit(struct soap* soap) {
soap_end(soap);
soap_free(soap);}
- Create SQL skeleton for User Defined Functions cy.sql.in:
CREATE OR REPLACE FUNCTION cy(OUT provider text, INOUT source text, INOUT dest text, OUT rate numeric)
RETURNS SETOF record
AS ‘MODULE_PATHNAME’, ‘cy’ LANGUAGE C IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION cy(text, text) TO public; - Create Makefile to comply with PostgreSQL build infrastructure for extensions (called PGXS):
MODULE_big = cy
OBJS = stdsoap2.o cyC.o cyClient.o cy.o
DATA_built = cy.sql
PG_CONFIG = /opt/pgsql/bin/pg_config
PG_CPPFLAGS = -I/opt/gsoap/include
PGXS := $(shell $(PG_CONFIG) –pgxs)
include $(PGXS)Plase take note that my PostgreSQL resides in /opt/pgsql.
- Build and install everything:
abdul@mylaptop:~/wsdl/cy$ make
abdul@mylaptop:~/wsdl/cy$ sudo make install
abdul@mylaptop:~/wsdl/cy$ /opt/pgsql/bin/psql -U admin -h localhost -p 5432 -d test -f cy.sql - Login to database server:
abdul@mylaptop:~/wsdl/cy$ /opt/pgsql/bin/psql -U slony -h localhost -p 5432 test
Password for user slony:
psql (9.0.4)
Type “help” for help.test=#
- Check my currency rate…
test=# SELECT * FROM cy(‘USD’,'IDR’);
provider | source | dest | rate
———-+——–+——+————
3 | USD | IDR | 9094.34819
4 | USD | IDR | 9087.5
AVERAGE | USD | IDR | 9090.92409
(3 rows)test=# SELECT * FROM cy(‘EUR’,'IDR’);
provider | source | dest | rate
———-+——–+——+——————
3 | EUR | IDR | 12020.0213983611
4 | EUR | IDR | 11993.8496462887
AVERAGE | EUR | IDR | 12006.9261827403
(3 rows)test=# SELECT * FROM cy(‘SGD’,'IDR’);
provider | source | dest | rate
———-+——–+——+——————
3 | SGD | IDR | 7272.91848469339
4 | SGD | IDR | 7257.45911065679
AVERAGE | SGD | IDR | 7265.18348117957
(3 rows)test=#
Hi, it comes from the Public Web Service thousands miles away from my house, not from my database.
very nice!