current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Postgres function:

CREATE OR REPLACE FUNCTION lcp_product_find(IN pi_prd_code text DEFAULT NULL::text, OUT  po_cursor refcursor, OUT po_err_num integer, OUT po_err_desc text)
RETURNS record AS
$BODY$  
DECLARE
v_proc_name text;
v_prd_id integer;
BEGIN
v_proc_name := 'lcp_product_find';
po_cursor := 'po_cursor';

-- some selects from lct_products table

OPEN po_cursor FOR 
    select "PRD_ID", "PRD_FAMILY", "PRD_NAME", "PRD_DESC", "PRD_BRAND", 
    "PRD_MODEL", "PRD_STATUS", "PRD_AUDIT_CD", "PRD_AUDIT_MD", "PRD_CODE" 
    from lct_products where "PRD_ID" = v_prd_id;
    RETURN;

END;
$BODY$
LANGUAGE plpgsql VOLATILE

In general, function returns product data in cursor (if any data found) and additionally err_number and err_desc if error occured.

Now I want to access from PHP data returned by function in po_cursor. What I am doing is:

<?php
$conn = new PDO("pgsql:host=localhost;port=5432;dbname=name", "user", "pas");

$conn->beginTransaction();

$prd_code = $_POST['prd_code'];     //echo $prd_code;

// call the function
$stmt = $conn->prepare("select lcp_product_find(:pi_prd_code)");
$stmt->bindParam('pi_prd_code', $prd_code, PDO::PARAM_STR);
$stmt->execute();
$cursors = $stmt->fetchAll();
$stmt->closeCursor();

?>

As a result I recive sth like that:

Array
(
[0] => Array
    (
        [lcp_product_find] => (po_cursor,,)
        [0] => (po_cursor,,)
    )

)

Can you help me in sorting that out? What am I doing wrong that I do not get data returned in po_cursor? Can it be done without PDO?

share|improve this question

1 Answer 1

up vote 0 down vote

The SQL cursor, once opened and returned to php, has to be handled by the SQL statements FETCH, MOVE, CLOSE, ... through its name. This functionality is not implemented as PDO functions and besides is not compatible across databases.

PDOStatement::closeCursor refers to client-side cursors which are not relevant in your context.

Example:

$s = $db->query("FETCH ALL FROM po_cursor");
$rows = $s->fetchAll();
$db->query("CLOSE po_cursor");

If using dynamic cursor names, be aware that they are SQL identifiers, syntaxically, which implies that they must be quoted the same as a column or table name when not strictly alphanumeric, and also that they're case-insensitive when not quoted.

pg_escape_identifier implements this quoting for the pg_* API but there doesn't seem to be an equivalent function in PDO.

When the program does not name cursors, PostgreSQL generates names like <unnamed portal 1>, whose spaces require quoting overall before being injectable into queries, like:

fetch all from "<unnamed portal 1>"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.