SOCI Cannot prepare statement

Question

I have a function like this:

CREATE OR REPLACE FUNCTION get_path_set_1(IN pathset_id_in character varying, OUT id character varying, OUT pathset_id character varying, OUT utility double precision)
  RETURNS SETOF record AS
$BODY$

    begin
        if exists(SELECT 1 FROM "PathSet_Scaled_HITS_distinctODs" WHERE "ID" = $1) then
            return query SELECT "ID", "PATHSET_ID", "UTILITY"
            FROM "SinglePath_Scaled_HITS_distinctODs"
            where "PATHSET_ID" = $1;
        end if; 
    end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION get_path_set_1(character varying)
  OWNER TO postgres;

when I call it in my program using this:

std::string testStr("43046,75502");// or std::string testStr("'43046,75502'");
soci::rowset<sim_mob::SinglePath> rs = (sql.prepare << "get_path_set_1(:pathset_id_in)",soci::use(testStr));

I get the following exception:

terminate called after throwing an instance of 'soci::postgresql_soci_error'
  what():  Cannot prepare statement. ERROR:  syntax error at or near "get_path_set_1"
LINE 1: get_path_set_1($1)

I will appreciate if you help me detect missing part thank you

Enable `log_statement = 'all'` in `postgresql.conf`, re-test, and show the exact SQL that gets run from the log file please. — Craig Ringer, Aug 11 '14 at 09:02

Erwin Brandstetter · Answer 1 · 2014-08-13T03:24:09.997

1

This does not solve the error you report. But simplify your function:

CREATE OR REPLACE FUNCTION get_path_set_1(pathset_id_in varchar)
  RETURNS TABLE(id varchar, pathset_id varchar, utility double precision) AS
$func$
BEGIN
   RETURN QUERY
   SELECT "ID", "PATHSET_ID", "UTILITY"
   FROM   "SinglePath_Scaled_HITS_distinctODs"
   WHERE  "PATHSET_ID" = $1;
END
$func$  LANGUAGE plpgsql;

RETURNS TABLE is the modern, more elegant, equivalent form of the combination RETURNS SETOF record and OUT parameters.
IF exists ... is buying you nothing here. Run the query; if nothing is found, nothing is returned. Same result for half the cost.

edited Aug 13 '14 at 03:24

answered Aug 11 '14 at 10:14

Erwin Brandstetter

605,456
145
1,078
1,228

erwin, yes the `RETURNS TABLE` is nicer. I modified my code accordingly. About the `IF exists`: the inner table `"SinglePath_Scaled_HITS_distinctODs"` is 20 times larger than the outer table. That is why we taught it would be better to search a smaller table and get into the larger table only if necessary. what do you think? – rahman Aug 13 '14 at 08:09
@rahman: First thing is correctness, if you want to depend on another table, you must reference it somehow. If that's not required, the size of the table does not matter much as long as it results in an index scan - which I would expect. But you don't have to take my word on it. Run both variants with `EXPLAIN ANALYZE` and see for yourself. – Erwin Brandstetter Aug 13 '14 at 12:52

score 0 · Accepted Answer · answered Aug 11 '14 at 12:03

From this piece of code:

soci::rowset<sim_mob::SinglePath> rs =
  (sql.prepare << "get_path_set_1(:pathset_id_in)",soci::use(testStr));

it appears you're trying to prepare a query that just contains the function call without even a SELECT.

That's not valid in SQL. You want to prepare this query instead:

 SELECT * FROM get_path_set_1(:pathset_id_in)

This form (select * from function(...)) is also necessary because the function returns a resultset with multiple columns, as opposed to just a scalar value.

Also as Erwin mentions, the OUT and SETOF RECORD are weird in this case, I'll second his advice on using RETURNS TABLE.

SOCI Cannot prepare statement

2 Answers2