I have a PostgreSQL function that selects data and returns it via a refcursor, similar to the following declaration:
CREATE OR REPLACE FUNCTION my_function()
RETURNS refcursor AS
...
How do I retrieve data from this function via a CodeIgniter model? I can't just SELECT directly from the function as it does not return the data directly.
In case anyone is interested, a post on php.net gave the following solution:
protected function dbquery($query){
pg_query("BEGIN;");
$tr=pg_query($query);
$r=pg_fetch_row($tr);
$name=$r[0];
$rs=pg_query("FETCH ALL IN \"" . $name . "\";");
pg_query("END;");
return $rs;
}
Which can be wired up in the model as follows:
$query = $this->dbquery("SELECT * FROM my_function()");
while ($row = pg_fetch_assoc($query))
{
array_push($result, array('my_column' => $row['my_column'] ));
}
Not an ideal solution as it does not use the CI Postgres driver's functions (although it probably could be refactored to), but it works.