-1

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.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
machineghost
  • 33,529
  • 30
  • 159
  • 234
  • "*I have several SQL statements in a file*" - who or what is writing that file? Who is reading it? (How) are you processing the file (e.g. run it as a script in `psql`)? – Bergi Jan 24 '23 at 22:02
  • I was more generally curious, but in this specific case I'm writing a SQL "unit test" with pgTAP. – machineghost Jan 24 '23 at 22:10

1 Answers1

1

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • Thanks, but how would I create the table programmatically, ie. not using standard input? Also, re:"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" ... In this case, I'm writing some pgTAP tests, so a bit of repetition is expected (and not necessarily indicative of a problem). – machineghost Jan 24 '23 at 20:52
  • @machineghost: `create table targets as select * from (values ('a'), ('b'), ...) as x(t);` –  Jan 24 '23 at 21:11