libpq and PQexecPrepared - Mailing list pgsql-general

I have been trying to use PQexecPrepared but so far have been having
little success. I have looked at the docs and there is very little in
there by way of examples. I am not really a native C programmer hence
my reason for assuming its my own fault and not a bug in PG but I have
noticed some things I just cannot explain.

Version == (PostgreSQL) 7.4.7

The following function has been used by me for some time from Perl

CREATE FUNCTION insert_index(varchar, integer, integer) RETURNS INTEGER AS '
DECLARE
 var_keyword        alias for $1;
 var_job_id         alias for $2;
 var_term_freq      alias for $3;
 var_exists         int4;
BEGIN
     SELECT into var_exists job_id
            FROM job_search_index
           WHERE keyword = var_keyword
             AND job_id = var_job_id;

        IF var_exists is null  THEN
             insert into job_search_index ( keyword , job_id, term_frequency )
                 values ( var_keyword, var_job_id, var_term_freq);
            return 1;
        else
             update job_search_index
                set term_frequency = var_term_freq
              where keyword = var_keyword
                and job_id  = var_job_id;
            return 2;
       END IF;
   RETURN 0;
   END;
' LANGUAGE 'plpgsql';


Its straight forward enough.

However, when I use the following from libpq

prepare = PQexec(conn,  "prepare insert_indx (varchar, integer,
integer) as select harry.insert_index($1, $2, $3)");

<snip some bits>
result = PQexecPrepared(conn,
                           "insert_indx",
                           3,
                           (const char* const *)paramValues,
                           paramLengths,
                           NULL,
                           1);


If I log the statements called I get the following

<snip>
[5-1] LOG:  statement: prepare insert_indx (varchar, integer, integer)
as select harry.insert_index($1, $2, $3)
[6-1] LOG:  statement: BEGIN
[7-1] LOG:  statement: SELECT  job_id FROM job_search_index WHERE
keyword =  $1  AND job_id =  $2
[7-2] CONTEXT:  PL/pgSQL function "insert_index" line 7 at select into variables
[8-1] LOG:  statement: SELECT   $1  is null
[8-2] CONTEXT:  PL/pgSQL function "insert_index" line 12 at if
[9-1] LOG:  statement: update job_search_index set term_frequency =
$1  where keyword =  $2  and job_id =  $3
[9-2] CONTEXT:  PL/pgSQL function "insert_index" line 16 at SQL statement
[10-1] LOG:  statement: SELECT  1
[10-2] CONTEXT:  PL/pgSQL function "insert_index" line 22 at return
</snip>

The params are correct for the entry in the logs at [7-1] but if you
look at [9-1] the params are in the wrong order. This looks odd to me
and I was wondering if someone could explain this?

I am assuming this could be an error in the way I am using libpq
because so far I have been unable to get libpq working when using
stored procs and prepared statements

As an aside are there any decent examples on using libpq online. The
ones in the docs are minimal at best and assume text parameters which
make things a bit too easy. I have also greped through the contrib
directories and most of the src in there seems to be using PQexec. I
would like to see a working example using PQexecPrepared with mixed
params if possible.

Regards,
Harry