Problem with array in plpgsql function .. please help :-)

[David Gagnon <dgagnon@xxxxxxxxxx>]

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';

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org