Thread: libpq and Datums management with embedded C function
I'm dealing witch a C function embedded into postgresql-9.0.2. I'm returning a set of rows and following the example here http://www.postgresql.org/docs/9.0/static/xfunc-c.html everything works fine, but only if I use the C string approach. I'm using libpq to retrieve data and something like this works fine: snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, 0)); because PQgetvalue returns an array of char. But if I want to get the original data as output, I have to deal with Datums and I don't know how to do this with lippq. Something like the following it doesn't work: Datum values[1]; values[0] = (Datum) PQgetvalue(funcctx->user_fctx, call_cntr, 1); snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, 0)); Have you got any idea of how to solve this...do you know another way to perform a query indide the db without involve libpq? I would prefer to use them because I can connect to several postgresql instances. Thank you. -- Alessandro Candini MEEO S.r.l. Via Saragat 9 I-44122 Ferrara, Italy Tel: +39 0532 1861501 Fax: +39 0532 1861637 http://www.meeo.it ======================================== "ATTENZIONE:le informazioni contenute in questo messaggio sono da considerarsi confidenziali ed il loro utilizzo è riservato unicamente al destinatario sopra indicato. Chi dovesse ricevere questo messaggio per errore è tenuto ad informare il mittente ed a rimuoverlo definitivamente da ogni supporto elettronico o cartaceo." "WARNING:This message contains confidential and/or proprietary information which may be subject to privilege or immunity and which is intended for use of its addressee only. Should you receive this message in error, you are kindly requested to inform the sender and to definitively remove it from any paper or electronic format."
On Thu, Feb 17, 2011 at 4:16 AM, Alessandro Candini <candini@meeo.it> wrote: > I'm dealing witch a C function embedded into postgresql-9.0.2. > > I'm returning a set of rows and following the example here > http://www.postgresql.org/docs/9.0/static/xfunc-c.html everything works > fine, but only if I use the C string approach. > > I'm using libpq to retrieve data and something like this works fine: > snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, 0)); > because PQgetvalue returns an array of char. > > But if I want to get the original data as output, I have to deal with Datums > and I don't know how to do this with lippq. > Something like the following it doesn't work: > > Datum values[1]; > values[0] = (Datum) PQgetvalue(funcctx->user_fctx, call_cntr, 1); > snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, 0)); > > Have you got any idea of how to solve this...do you know another way to > perform a query indide the db without involve libpq? > I would prefer to use them because I can connect to several postgresql > instances. Have you ruled out dblink? Also for connecting to self it would be preferable to use SPI (http://www.postgresql.org/docs/9.0/static/spi.html) vs libpq. merlin
I see that dblink is 2500 lines more or less... Is there anyone how to set up correctly the 2 lines I posted before? I will explore SPI anyway hoping it will gett my life easier... > On Thu, Feb 17, 2011 at 4:16 AM, Alessandro Candini<candini@meeo.it> wrote: >> I'm dealing witch a C function embedded into postgresql-9.0.2. >> >> I'm returning a set of rows and following the example here >> http://www.postgresql.org/docs/9.0/static/xfunc-c.html everything works >> fine, but only if I use the C string approach. >> >> I'm using libpq to retrieve data and something like this works fine: >> snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, 0)); >> because PQgetvalue returns an array of char. >> >> But if I want to get the original data as output, I have to deal with Datums >> and I don't know how to do this with lippq. >> Something like the following it doesn't work: >> >> Datum values[1]; >> values[0] = (Datum) PQgetvalue(funcctx->user_fctx, call_cntr, 1); >> snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, 0)); >> >> Have you got any idea of how to solve this...do you know another way to >> perform a query indide the db without involve libpq? >> I would prefer to use them because I can connect to several postgresql >> instances. > Have you ruled out dblink? Also for connecting to self it would be > preferable to use SPI > (http://www.postgresql.org/docs/9.0/static/spi.html) vs libpq. > > merlin -- Alessandro Candini MEEO S.r.l. Via Saragat 9 I-44122 Ferrara, Italy Tel: +39 0532 1861501 Fax: +39 0532 1861637 http://www.meeo.it ======================================== "ATTENZIONE:le informazioni contenute in questo messaggio sono da considerarsi confidenziali ed il loro utilizzo è riservato unicamente al destinatario sopra indicato. Chi dovesse ricevere questo messaggio per errore è tenuto ad informare il mittente ed a rimuoverlo definitivamente da ogni supporto elettronico o cartaceo." "WARNING:This message contains confidential and/or proprietary information which may be subject to privilege or immunity and which is intended for use of its addressee only. Should you receive this message in error, you are kindly requested to inform the sender and to definitively remove it from any paper or electronic format."
On Fri, Feb 18, 2011 at 2:04 AM, Alessandro Candini <candini@meeo.it> wrote: >> On Thu, Feb 17, 2011 at 4:16 AM, Alessandro Candini<candini@meeo.it> >> wrote: >>> >>> I'm dealing witch a C function embedded into postgresql-9.0.2. >>> >>> I'm returning a set of rows and following the example here >>> http://www.postgresql.org/docs/9.0/static/xfunc-c.html everything works >>> fine, but only if I use the C string approach. >>> >>> I'm using libpq to retrieve data and something like this works fine: >>> snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, >>> 0)); >>> because PQgetvalue returns an array of char. >>> >>> But if I want to get the original data as output, I have to deal with >>> Datums >>> and I don't know how to do this with lippq. >>> Something like the following it doesn't work: >>> >>> Datum values[1]; >>> values[0] = (Datum) PQgetvalue(funcctx->user_fctx, call_cntr, 1); >>> snprintf(values[0], 16, "%s", PQgetvalue(funcctx->user_fctx, call_cntr, >>> 0)); >>> >>> Have you got any idea of how to solve this...do you know another way to >>> perform a query indide the db without involve libpq? >>> I would prefer to use them because I can connect to several postgresql >>> instances. >> >> Have you ruled out dblink? Also for connecting to self it would be >> preferable to use SPI >> (http://www.postgresql.org/docs/9.0/static/spi.html) vs libpq. >> >> merlin > > I see that dblink is 2500 lines more or less... well, dblink is a library that manages some of the tedious work of implementing libpq inside of a C procedure. A lot of the code deals with connection management and the tedious work of creating the result set. > Is there anyone how to set up correctly the 2 lines I posted before? Well, for starters, you can't sprintf into a Datum. A Datum is a void* more or less to an internal postgresql stucture which has nothing at all whatsoever to do with what libpq returns. As data comes or goes out through libpq, it is translated to/from text or binary, depending on if libpq is using the text or binary protocol. When dealing with data in the backend in this way, text is definitely easier. You can translate data to/from Datums using type conversion functions that you can look up by type oid and execute. I am skeptical though that you really need to do that. > I will explore SPI anyway hoping it will gett my life easier... SPI is a way of executing queries *against the database the procedure is executing from*, and doesn't use libpq. If you are doing cross database queries, I would strongly advise to simply load dblink and start using it -- it's very mature and works well. merlin