PostgreSQL: How to pass and array to a function and using it in a query with the IN operator

Question

I have a problem, I want to pass an array to a postgres function and use that array so returns values in a SELECT IN clause.

But It shows me this error:

An error occurred executing the SQL command :
SELECT
      *
  FROM
      get_invtransferences_porders_fporders (300001300 , array [ 300093753 , 300094126 , 300093349 , 300093838 , 300094128 ] ...

ERROR: operator does not exist : integer = integer [ ]
  Hint : No operator matches the name and type of arguments. You may need to add explicit type conversions .
  Where : PL / pgSQL get_invtransferences_porders_fporders (numeric , integer []) function on line 8 FOR loop around rows of a SELECT

This is my function:

CREATE OR REPLACE FUNCTION public.get_invtransferences_porders_fporders(p_product_id numeric, P_INVTRANSFERENCES_IDS integer[])
  RETURNS SETOF record
  LANGUAGE plpgsql
AS
$body$
DECLARE
    PORDER_PRODUCT RECORD;
    COMPONENT RECORD;
    COMPONENT2 RECORD;
    COMPONENT3 RECORD;
BEGIN
    FOR PORDER_PRODUCT IN (
                      SELECT 
                        'porder'                                                    AS "operation_type"
                        ,porders.id                                                 AS "porder_id"
                        ,porders.user_id                                            AS "porder_user_id"
                        ,(SELECT name FROM users WHERE users.id = porders.user_id)  AS "porder_user_name"
                        ,porders.delivery_datetime                                  AS "porder_delivery_datetime"
                        ,porders_products.requested                                 AS "product_requested"
                        ,porders_products.produced                                  AS "product_produced"
                        ,products.code                                              AS "product_code"
                        ,products.NAME                                              AS "product_name"
                        ,(
                          SELECT products.name
                          FROM products
                          WHERE id = product_components.component_product_id
                          )                                                         AS "component_product_name"
                        ,product_components.quantity                                AS "component_quantity"
                        ,(
                          SELECT products.um_id
                          FROM products
                          WHERE id = product_components.component_product_id
                          )                                                         AS "component_um_id"
                        ,(product_components.quantity / products.production_base) * porders_products.requested  AS "total"
                      FROM porders
                        ,porders_products
                        ,products
                        ,product_components
                      WHERE porders.id = porders_products.porder_id
                        AND porders_products.product_id = products.id
                        AND porders_products.product_id = product_components.product_id
                        AND porders.id IN (
                          SELECT rawm_audit_porders.porder_id
                          FROM rawm_audit_invtransferences
                            ,rawm_audits
                            ,rawm_audit_porders
                          WHERE rawm_audit_invtransferences.rawm_audits_id = rawm_audits.id
                            AND rawm_audit_porders.rawm_audits_id = rawm_audits.id
                            AND rawm_audit_invtransferences.invtransference_id IN
                            (
                              SELECT 
                                invtransferences.id
                              FROM invtransferences
                                ,invtransferences_products
                                ,products
                              WHERE invtransferences.id = invtransferences_products.invtransference_id
                                AND products.id = invtransferences_products.product_id
                                AND invtransferences.id IN (P_INVTRANSFERENCES_IDS)
                            )
                          )
                        AND product_components.component_product_id = p_product_id
                 ) LOOP
        IF(PORDER_PRODUCT.porder_id IS NOT NULL)THEN
            RETURN NEXT PORDER_PRODUCT;
        END IF;
    END LOOP;
    RETURN;         
END;
$body$
 VOLATILE
 COST 100
 ROWS 1000

I think the error it here `invtransferences.id IN (P_INVTRANSFERENCES_IDS)

This is the select that calls the function:

SELECT  
      *
  FROM 
      get_invtransferences_porders_fporders(300001300 , array[300093753, 300094126, 300093349, 300093838, 300094128] )
  AS
  (
       "operation_type" varchar,
       "porder_id" numeric,
       "porder_user_id" numeric,
       "porder_user_name" varchar,
       "porder_delivery_datetime" date,
       "product_requested" numeric,
       "product_produced" numeric,
       "product_code" varchar,
       "product_name" varchar,
       "component_product_name" varchar,
       "component_quantity" numeric,
       "component_um_id" varchar,
       "total" numeric
   )
  ORDER BY
      "porder_id";

EDIT: I remove the VARIADIC words that were in the function and in the select that calls the function

Can you hep me Please.

max taldykin · Accepted Answer · 2016-02-19T18:43:12.010

1

You don't need to declare your function as VARIADIC to pass array to it.

Try this

CREATE OR REPLACE FUNCTION xxx(
    p_product_id integer,
    P_INVTRANSFERENCES_IDS integer[])
  RETURNS SETOF record
  LANGUAGE sql
AS
$body$
    select p_product_id = ANY(P_INVTRANSFERENCES_IDS)
$body$;

Note there is no VARIADIC before P_INVTRANSFERENCES_IDS.

You also need to use ANY instead of IN to check membership in array.

SqlFiddle

edited Feb 19 '16 at 18:43

answered Feb 19 '16 at 18:28

max taldykin

12,459
5
45
64

I thnik the problem is here AND invtransferences.id IN (P_INVTRANSFERENCES_IDS – juanpscotto Feb 19 '16 at 18:36
Awesome!!! Thanks!! whats the difference between IN and ANY(). In the docs says IN is equivalent to = ANY. – juanpscotto Feb 19 '16 at 18:48
PG manual has quite readable explanation in [9.23. Row and Array Comparisons](http://www.postgresql.org/docs/9.4/static/functions-comparisons.html). Also arrays are often much [faster](http://stackoverflow.com/questions/14987321). – max taldykin Feb 19 '16 at 18:54

PostgreSQL: How to pass and array to a function and using it in a query with the IN operator

1 Answers1