Operator in postgres function returns SQL Error [42883]: ERROR: operator does not exist: integer = integer[]

Question

I have the following error in a function

postgres SQL Error [42883]: ERROR: operator does not exist: integer = integer[]

I tried to convert the data to varchar and text but still I could not return the necessary data.

Follow code to analyze:

CREATE OR REPLACE FUNCTION public.ignicaoligada(datainicial timestamp, datafinal timestamp, idmot integer, id integer[])
RETURNS TABLE(bidataevento timestamp without time zone, monome character varying, bicodigo integer, biplaca character varying)
LANGUAGE plpgsql
AS $function$
        declare
            dataini timestamp;
            nome text;
            s varchar;
            contador integer;
        begin
                contador = 0;
                nome := monome;
                for bidataevento, monome, bicodigo, biplaca in select  bi.bidataevento, mo.mtnome, bi.bicodigo, bi.biplaca from bilhetes bi
                        join motoristas mo on mo.mtcodigo = bi.bimotorista
                        join veiculos on veplaca = bi.biplaca
                        where bi.bidataevento > '28/03/2017'
                        and  bi.bidataevento <  '29/03/2017' 
                        and bi.biignicao = 1
                        and mo.mtcliente = 1
                        and vecodigo in (id)
                        order by bidataevento asc

                    LOOP
                        dataini := bidataevento;
                        if (select bl.biignicao from bilhetes bl where bl.bidataevento < dataini order by bl.bidataevento desc limit 1) = 0 THEN
                                RETURN NEXT;
                            contador := contador + 1;
                        END IF;
                    END LOOP;

                RETURN;
            end
        $function$

Answer 1

Use the unnest(anyarray) function, it will expand the array into several lines.

Documentation: https://www.postgresql.org/docs/9.2/static/functions-array.html

Try it like this:

CREATE OR REPLACE FUNCTION public.ignicaoligada(datainicial timestamp, datafinal timestamp, idmot integer, id integer[])
RETURNS TABLE(bidataevento timestamp without time zone, monome character varying, bicodigo integer, biplaca character varying)
LANGUAGE plpgsql
AS $function$
        declare
            dataini timestamp;
            nome text;
            s varchar;
            contador integer;
        begin
                contador = 0;
                nome := monome;
                for bidataevento, monome, bicodigo, biplaca in select  bi.bidataevento, mo.mtnome, bi.bicodigo, bi.biplaca from bilhetes bi
                        join motoristas mo on mo.mtcodigo = bi.bimotorista
                        join veiculos on veplaca = bi.biplaca
                        where bi.bidataevento > '28/03/2017'
                        and  bi.bidataevento <  '29/03/2017' 
                        and bi.biignicao = 1
                        and mo.mtcliente = 1
                        and vecodigo in (select unnest(id))
                        order by bidataevento asc

                    LOOP
                        dataini := bidataevento;
                        if (select bl.biignicao from bilhetes bl where bl.bidataevento < dataini order by bl.bidataevento desc limit 1) = 0 THEN
                                RETURN NEXT;
                            contador := contador + 1;
                        END IF;
                    END LOOP;

                RETURN;
            end
        $function$

Answer 2

Substitute IN for = ANY, with the clause ANY it is possible to check if a value exists in an array.

https://www.postgresql.org/docs/current/static/functions-subquery.html

CREATE OR REPLACE FUNCTION public.ignicaoligada(datainicial timestamp, datafinal timestamp, idmot integer, id integer[])
RETURNS TABLE(bidataevento timestamp without time zone, monome character varying, bicodigo integer, biplaca character varying)
LANGUAGE plpgsql
AS $function$
declare
    dataini timestamp;
    nome text;
    s varchar;
    contador integer;
begin
        contador = 0;
        nome := monome;
        for bidataevento, monome, bicodigo, biplaca in select  bi.bidataevento, mo.mtnome, bi.bicodigo, bi.biplaca from bilhetes bi
                join motoristas mo on mo.mtcodigo = bi.bimotorista
                join veiculos on veplaca = bi.biplaca
                where bi.bidataevento > '28/03/2017'
                and  bi.bidataevento <  '29/03/2017' 
                and bi.biignicao = 1
                and mo.mtcliente = 1
                and vecodigo = any(id)
                order by bidataevento asc

            LOOP
                dataini := bidataevento;
                if (select bl.biignicao from bilhetes bl where bl.bidataevento < dataini order by bl.bidataevento desc limit 1) = 0 THEN
                        RETURN NEXT;
                    contador := contador + 1;
                END IF;
            END LOOP;

        RETURN;
    end
$function$