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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have the following error in a function

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

I have tried to convert the data to varchar and text but even then I could not return the required data.

Follow code for analysis:

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$

sql postgresql plpgsql

asked by anonymous 29.05.2017 / 21:37

2 answers

Polygon return How can I test the connectivity between devices on a network without using the ICMP protocol?

score 0 · Answer 1

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

Documentation: link

Try 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$

score 0 · Answer 2

Replace IN with = ANY , with the ANY clause you can check if a value exists in an array.

link

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$