Problem with array in plpgsql function .. please help :-) - Mailing list pgsql-general

Hi all,

  I cannot find what is the problem with my function below.  The
following line in the function  :    AND PD.PDPONUM = ANY (receivingIds)

don't work.  If I change this line by    AND PD.PDPONUM = 1734 (Hardcode
a given value) I get a result row.

When I call the same function

 select * from usp_Commande_Dues_Retourner('{\'1734\'}', 'M',
'2005-02-02', '2005-11-02', 'EN' );

with the real line I get no result?

Any idea? What is the difference between AND PD.PDPONUM = 1734      and
AND PD.PDPONUM = ANY (receivingIds)



Thanks for your help .. I'm messing around this problem for several
hours now .. and haven't found the answer yet.

Best Regard
/David



CREATE OR REPLACE FUNCTION usp_Commande_Dues_Retourner(VARCHAR[],
VARCHAR, DATE, DATE, VARCHAR) RETURNS refcursor  AS $$
DECLARE

     receivingIds ALIAS FOR $1;
     companyId ALIAS FOR $2;
     fromReceptionDate ALIAS FOR $3;
     toReceptionDate ALIAS FOR $4;
     warehouseId ALIAS FOR $5;
BEGIN

    OPEN ref FOR SELECT BD.BDNUM, BDYPNUM, BORRNUMC, RRDESC, BONUM,
BD.BDDTDUEA, BD.BDICNUM, (BD.BDPRIXNET * BD.BDQAEXPV) AS Total,
BD.BDQAEXPV, IQQSTOCK - IQQRESV AS IQQSTOCK, BD.BDDTDUEV, T_IC2.ICQTE
                    FROM BD INNER JOIN (

                  SELECT BDICNUM, SUM(BDQAEXPV) AS ICQTE
                  FROM BD
                  INNER JOIN BO ON BD.BDBONUM = BO.BONUM AND BD.BDYPNUM
= BO.BOYPNUM
                  INNER JOIN PD ON BD.BDNUM = PD.PDBDNUM AND BD.BDYPNUM
= PD.PDYPNUM
                  WHERE BDSTATV = 3
                        AND BDAENUM = warehouseId
                        AND BOTYPE = 0
                        AND BOSTATUT IN (0, 1)
                        AND fromReceptionDate::DATE  <=  BODTCOM::DATE
                        AND toReceptionDate::DATE  >=  BODTCOM::DATE
                           AND PD.PDPONUM = ANY (receivingIds)
                        AND BD.BDYPNUM = companyId
                  GROUP BY BDICNUM

                    ) AS T_IC2 ON BD.BDICNUM = T_IC2.BDICNUM
                    INNER JOIN BO ON BD.BDBONUM = BO.BONUM  AND
BD.BDYPNUM = BO.BOYPNUM
                    INNER JOIN RR ON BO.BORRNUMC = RR.RRNUM
                    LEFT OUTER JOIN IQ ON BD.BDICNUM = IQ.IQICNUM AND
BD.BDAENUM = IQ.IQAENUM
                WHERE BD.BDSTATV = 3
                AND BDAENUM = warehouseId
                AND BOTYPE = 0
                AND BOSTATUT IN (0, 1)
                AND fromReceptionDate::DATE <=  BODTCOM::DATE
                AND toReceptionDate::DATE >=  BODTCOM::DATE
                AND BD.BDYPNUM = companyId
                ORDER BY BONUM, BDICNUM, BDQAEXPV ;
    RETURN ref;

END;
$$ LANGUAGE 'plpgsql';