How to store a list of integers as variable in PostgreSQL

Question

I am just wondering how to store a list of integers as a variable in PostreSQL stored procedure.

For example, I have statements like these:

   select A from B where C IN (1,2,3);
   select A from B where C IN (1,2);

And I want to declare a variable to store (1,2,3) or (1,2).

So I would end up with a statement like:

select A from B where C in numberList;

(numberList has the value (1,2,3))

I don't know which datatype I should use,I looked up online and can't find there is a list type in psql. And what's the syntax for that as well?

Answer 1

You can store them as an integer array (i.e. int[]) type, and then invoke using the ANY operator as such:

WITH RECURSIVE CTE AS (
    SELECT 1 AS i
        UNION ALL
    SELECT i+1 FROM CTE WHERE i < 15
)
SELECT * FROM CTE WHERE i = ANY( ARRAY[1, 5, 7, 9] )

which yields back the piecewise-dynamic IN operator result we're looking for:

i
-
1
5
7
9

Answer 2

with myconstants (i) as (values(1),(2),(3))
select A from B, myconstants where C=any(i);

further reading