Subject: PQExec inside a C function?

From: [email-masked] (Bruce Thomson)
Date: Tue, 28 Jun 1994 15:31:19 +1200 (NZST)

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?

Thanks for any help.

-----------------------------------------------

Welcome to the POSTGRES terminal monitor

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
 
Query sent to backend is "retrieve (result=insert_row(5))"
FATAL: no response from backend: detected in dump_data
 
Go 
* retrieve (twoval.all)\g
 
Query sent to backend is "retrieve (twoval.all)"
FATAL: no response from backend: detected in PQexec
 
Go 
* \q
Broken pipe

-----------------------------
#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);
}
-------------------------------

-----------------------------------------------------------------------------

Bruce Thomson                                     [email-masked]
                                                           e-mail for pgp key
-----------------------------------------------------------------------------

==============================================================================
   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] (Paul M. Aoki)
Date: Mon, 27 Jun 94 23:48:46 -0700

> PQexec("begin");
          ^^^^^
> sprintf(append_line,"append twovals(x1=%d,x2=%d)",x,x*2);
          ^^^

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.

in general this interface has a lot of problems.  i don't particularly
recommend it though the alternative (direct access method calls) isn't
very nice either.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720-1776      |  ...!uunet!ucbvax!aoki