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
