Home > PostgreSQL > Consume SOAP Web Service from PostgreSQL User Defined Function in C

Consume SOAP Web Service from PostgreSQL User Defined Function in C

In memoriam, Pipit F.Y.H (Oct 30, 1968 – Jan 15, 2012), we have passed precious moments, once in my lifetime.

f.y.h

I have took a look directory of public SOAP web services at http://www.service-repository.com. Then an idea came across my mind to utilize gSOAP toolkit to develop some C code for a User Defined Function in PostgreSQL, so I can consume service provided by http://www.currencyserver.de/webservice/currencyserverwebservice.asmx?WSDL to get currency rate through SQL command, such as SELECT provider, source, dest, rate FROM cy(‘USD’, ‘IDR’).
  • 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:
  • 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.
About these ads
Categories: PostgreSQL
  1. Merlin Moncure
    February 5, 2013 at 6:15 pm

    very nice!

  2. anwar
    September 10, 2013 at 4:04 pm

    Would i do the same for Netezza UDX?

  1. March 4, 2012 at 6:34 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: