Use a variable into "in" clause postgres

Question

I need to create a function that has a variable, and that variable I can use in the 'where' clause, and the variable must be of the 'in' type

See the variable varGeraMovGer and the use in "where"

    CREATE OR REPLACE FUNCTION public.gera_pend()
  RETURNS varchar AS
$BODY$
DECLARE
   varGeraMovGer varchar := ('112301','112451');
BEGIN  
    INSERT INTO MOVCON (MCON_TRANSACAO, MCON_OPERACAO, MCON_STATUS, MCON_DATALCTO, MCON_DATAMVTO, MCON_PCON_CONTA, MCON_UNID_CODIGO, MCON_VALOR, MCON_DC, MCON_HIST_CODIGO, MCON_COMPLEMENTO, MCON_NUMERODCTO)
    SELECT SV_TRANSACAO
         , SV_TRANSACAO || NEXTVAL('SV_OPERACAO')
         , CAST('N' AS VARCHAR)
         , SV_DATA_CONTABIL
         , SV_DATA_CONTABIL
         , SV_CONTA_DEBITO
         , CAST('001' AS VARCHAR)
         , SUM(SV_VALOR_CONTABIL)
         , (CASE WHEN SV_TIPO_DEBITO = 'DEVEDORA' THEN 'D' ELSE 'C' END)
         , CAST('9999' AS VARCHAR)
         , SV_CLASSIFICADOR
         , CAST('0606' AS VARCHAR) 
      FROM SV_IMPORTACAO
     WHERE SV_CONTA_DEBITO IN varGeraMovCon
     GROUP BY SV_TRANSACAO, SV_DATA_CONTABIL, SV_CONTA_DEBITO, SV_TIPO_DEBITO, SV_CLASSIFICADOR;

     RETURN 'FIM PROCESSAMENTO';

    END IF; 

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.gera_pend()
  OWNER TO erp;

The postgres return:

ERROR:  syntax error at or near "varGeraMovCon"
LINE 32:      WHERE SV_CONTA_DEBITO IN varGeraMovCon

One option is to pass an array and use array functions instead of in. — Gordon Linoff
– Gordon Linoff, Commented Nov 24, 2020 at 12:15

user330315 · Accepted Answer · 2020-11-24 12:16:46Z

3

You need an array

DECLARE
   vargeramovger text[] := array['112301','112451'];
begin 
 ...
 WHERE SV_CONTA_DEBITO = ANY(vargeramovger);
 ...
end;

Note that if SV_CONTA_DEBITO is a "number" (e.g. integer or bigint) you need to change the type of the array:

vargeramovger int[] := array[112301,112451];

answered Nov 24, 2020 at 12:16

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use a variable into "in" clause postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related