Postgres Stored Procedures returing cursor in birt report

Question

Postgres Stored Procedures returing cursor.

I have store procedure sp_order_tracker4. I call this store procedure from pgsql using below

SELECT sp_order_tracker4('',0,0,'cities_cur');
FETCH ALL IN "cities_cur";

cities_cur is cursor name. How will I get birt report from this.

Answer 1

I had some problems when the stored procedure return ref-cursor. Then I returned TABLE, for example:

1. SP:

   CREATE OR REPLACE FUNCTION REP.GET_ALL_DESCENDANTS(parentId integer) RETURNS TABLE (diag_id int8,mkb10_diag_code varchar,mkb10_diag_nm varchar, dsc varchar,parent_id int8,hierarchy_lvl int2,ppn_dt Date,ppn_tm Time) AS ...

   Birt DataSet:

   SELECT * FROM REP.GET_ALL_DESCENDANTS(?);

2. SP:

   CREATE OR REPLACE FUNCTION REP.string_to_rows(tablename text, tablecolumn text, param text) RETURNS table(parameters integer) AS' ...

   Birt DataSet: ... WHERE human.human_id IN((SELECT "Col" FROM rep.string_to_rows('dwh.human', 'human_id', ?) d("Col")))

I hope this will help you.