In a programming language like Javascript, instead of doing:

['a', 'b', 'c', 'd'].map(...);
['a', 'b', 'c', 'd'].filter(...);
['a', 'b', 'c', 'd'].forEach(...);

I can do the following:

const names = ['a', 'b', 'c', 'd'];

names.map(...);
names.filter(...);
names.forEach(...);

If I have several SQL statements in a file:

SELECT * FROM foo WHERE something IN ('a', 'b', 'c', 'd');
SELECT * FROM bar WHERE something_else IN ('a', 'b', 'c', 'd');
SELECT * FROM baz WHERE another_thing IN ('a', 'b', 'c', 'd')

Is there a similar way I can "create an array variable" and then use it repeatedly in all those queries? I know things get complicated because ('a', 'b', 'c', 'd') isn't actually an array, and I'm not sure if I should be using a literal variable, a view, or a function to hold the ('a', 'b', 'c', 'd') part.

CodePudding user response：

The closest analogy would be a temporary table.

CREATE TEMP TABLE targets (t text);
COPY targets FROM stdin;
a
b
c
d
...thousands more rows
\.

SELECT foo.* FROM foo JOIN targets ON foo.x = targets.t

However, it is less common in a database to need to match one set of values against multiple tables because that can imply your database structure needs reworking.