CODEWITHSUNDEEP
postgresql
James Xia
James Xia

Reputation: 173

How to store a list of integers as variable in PostgreSQL

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?

Upvotes: 14

Views: 20414

Answers (4)

user3450233
user3450233

Reputation: 1

As said Jian but easier:

WITH numberList AS (values (1),(2))
SELECT A FROM B WHERE C = any(select * from numberList)

Upvotes: 0

GeekInDisguise
GeekInDisguise

Reputation: 1567

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.

Upvotes: 0

jian
jian

Reputation: 4877

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

further reading

Upvotes: 0

JJ Ward
JJ Ward

Reputation: 143

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

Upvotes: 2

Related Questions