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

Answer 1

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.

Answer 2

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.