Subject: Re: PQExec inside a C function?

From: [email-masked] (J.Plewe)
Date: Tue, 28 Jun 94 09:47:08 +0200

> 
> I am trying to call a user function from within the monitor that should
> do some PQExec'd insertions into a table.
> 
> The function is shown below.
> 
> 
> 
> Unfortunately, I get the following results from 'monitor'. 
> Can I execute embedded postquel statements in this way?
> 
> If not, what would be the best way to eventually call an external program,
> and insert its results (from an output file) into a table, all from a
> monitor retrieve command?
> 
> Go 
> * define function isert_row (language="C", returntype=int4)
> arg is (int4)
> as "/home/sun/bruce/postgres/pqexec/insert.so"\g
>  
> Query sent to backend is "define function isert_row (language="C", returntype=int4) arg is (int4) as "/home/sun/bruce/postgres/pqexec/insert.so""
> DEFINE
> Go 
> * load "/home/sun/bruce/postgres/pqexec/insert.so"\g
>  
> Query sent to backend is "load "/home/sun/bruce/postgres/pqexec/insert.so""
> LOAD
> Go 
> * retrieve (result=insert_row(5))\g
>  
> -----------------------------
> #include <stdio.h>
> #include "tmp/libpq.h"
> 
> int insert_row(x)
> int x;
> {
> char append_line[100];  /* care not to run over the length */
> 
> PQsetdb ("external");
> PQexec("begin");
> sprintf(append_line,"append twovals(x1=%d,x2=%d)",x,x*2);
> PQexec(append_line);
> PQexec("end");
> PQfinish(); 
> return(0);
> }
> -------------------------------

Hi!

I wonder how you managed to come so far, because I dealt with a similar
problem this night on my linux-pg4.2.

I did some investigations this morning and found some things about this:

Using the functions PQsetdb() and PQfinish() should cause some trouble,
because theire symbols are not defined in the postgres executable.
So you have to link your object with libpq.a.

This on the other hand should cause trouble using the 'load' command,
because now you have duplicate symbols (e.g. PQexec() in the postgres
executable as well as in your dot-oh file).

Maybe its legal to write such functions omitting PQsetdb() and PQfinish()?

The following PQxxxx-Symbols are defined in the postgres executable:

nm postgres | grep PQ
000175ec T _GetPQChar
00017660 T _GetPQInt4
000176f4 T _GetPQStr
0011bc90 B _PQAsyncNotifyWaiting
00046ef0 t _PQGetTupleBlock
00047224 T _PQappendNotify
000470e4 T _PQcleanNotify
000470b4 T _PQclear
0011ac90 B _PQerrormsg
00044f74 T _PQexec
00044e70 T _PQfn
00046cf8 T _PQfname
00046a04 T _PQfnameGroup
00046c6c T _PQfnumber
00046978 T _PQfnumberGroup
00046d94 T _PQftype
00046b48 T _PQgetgroup
00047074 T _PQgetlength
00045980 T _PQgetline
00047034 T _PQgetvalue
00046aa0 T _PQgroup
00046c04 T _PQnfields
00046910 T _PQnfieldsGroup
00046864 T _PQngroups
00046850 T _PQninstances
000468f8 T _PQninstancesGroup
000471fc T _PQnotifies
00047180 T _PQnotifies_init
00046620 T _PQnportals
00046824 T _PQntuples
00046890 T _PQntuplesGroup
000467a0 T _PQparray
000466c0 T _PQpnames
00045a5c T _PQputline
0004721c T _PQremoveNotify
000467f8 T _PQrulep
00046e38 T _PQsametype
00046604 T _PQtrace
000eff9c D _PQtracep
00046614 T _PQuntrace
000175c8 T _SetPQSocket
00045000 T _pqtest_PQexec
00045244 T _pqtest_PQfn

As a guess, perhaps you try your source without PQsetdb() and PQfinish()???

- Joerg

*********************************************************************
Dipl. Phys. Joerg Plewe               [email-masked]
MPI fuer molekulare Physiologie
Rheinlanddamm 201
44139 Dortmund
Germany                               +49 (0)231 1206 384
*********************************************************************

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked] (J.Plewe)
Date: Tue, 28 Jun 94 13:20:21 +0200

> [email-masked] (Bruce Thomson) writes:
>           ^^^^^
>           ^^^
> if this is postgres 4.x, x<2, this won't work at all.
> 
> if this is 4.2, you stand some chance but you don't want to use begin/end.
> (all backend "libpq" calls are executed in the same transaction already.)
> you also don't want to link your function with libpq, *ever*, if it's going
> to be used in the backend -- there are equivalent functions in the backend.
> i don't think you need to do PQsetdb since you're already in a backend
> that is attached to a database.

To my opinion, PQsetdb ist *not* defined in the backend, so you cannot even 
use it.

I tried to do a PQexec without PQsetdb, and it worked as expected.

- Joerg