how to call a pg function with libpq and get the param value

Question

ALL I have a postgresql function,so this:

CREATE OR REPLACE FUNCTION query_callouts(

    INOUT io_cursor_ref refcursor,
    INOUT opstatus integer,
    INOUT errtext character varying)
  RETURNS record AS
$BODY$
DECLARE

BEGIN

  OPEN $1 FOR
    SELECT tablename FROM pg_tables limit 10;
    --SELECT * from call_out_numbers;

  RETURN;
  Exception
    When Others Then
        GET STACKED DIAGNOSTICS opstatus = RETURNED_SQLSTATE,
                               errText = MESSAGE_TEXT;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION query_callouts(refcursor, integer, character varying)
  OWNER TO postgres;

I want to known ,how to use libpq in my c codes to visit the function - query_callouts and get the param io_cursor_ref and opstatus and errtext?

Answer 1

You can call the function just like you are executing any query:

select * from query_callouts('mycur', 0, '');

 io_cursor_ref | opstatus | errtext
---------------+----------+---------
 mycur         |        0 |
(1 row) 

opstatus and errtext would be set to the appropriate values if an exception occurs. io_cursor_ref contains the name you have passed to the function.

Internally, a refcursor value is simply the string name of a so-called portal containing the active query for the cursor. This name can be passed around, assigned to other refcursor variables, and so on, without disturbing the portal.

Note, you can use a refcursor only within a transaction.

All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to reference an open cursor only until the end of the transaction.

You can use explicit transaction:

begin;
select * from query_callouts('mycur', 0, '');
fetch all in mycur;
-- save or show the query result
-- and finally
commit;

or use mycur inside a function.

The quotes are from the documentation.