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

Answer 3

With this solution you can use the "variable" in several queries. Note that it is actually a function, but you can use it as a variable.

Also consider that each time you use it, the function will be executed, therefore it may not be the most efficient way, but for simple uses it helps to avoid typing or pasting the same list many times in different queries.

CREATE FUNCTION get_constant_array()
RETURNS INTEGER[]
AS $$
BEGIN
    RETURN ARRAY[1,2,3,4];
END;
$$ LANGUAGE plpgsql;

Then you can use it, for example, like this:

SELECT get_constant_array();

or like this:

SELECT * from sometable where some column in (SELECT get_constant_array) 

PS: This solution is based on PostgreSQL, although I think it may work, perhaps with little changes, in other dialects as well.

PPS: With a similar approach you can also store a list of strings, let me know if you need me to edit my answer to use strings instead.