Reputation: 318
I am programmatically writing SQL queries from user input. I want to show the user for each row returned, which part of their supplied query information matched against that row.
For instance user supplied information looks like:
red:[11,202]
blue:[36]
green:[202]
yellow:[11,36]
This information would build the following query:
SELECT name, list FROM test WHERE list SIMILAR TO '%(11|202|36)%'
But I would like to add a result field similar to the "Matched Element shown below:
name | list | Matched Element |
---|---|---|
foobar | 15,11,19,20 | red, yellow |
hello | 17,30,36,20 | blue, yellow |
bar | 101,202,330,460 | red, green |
test | 15,36,23 | blue |
I cannot add attempted examples as I do not know the proper language by which to seach for information. My current solution so far is to process the data row by row after the query has returned the result to my script to add the new column, but I would like to know if its possible to achieve this using postgres.
Here is a db-fiddle: https://www.db-fiddle.com/f/nMMB3wVFRTGeZgobmA4F6k/1
I am running postgresql 12.3 however I am able to change version if needed.
Upvotes: 1
Views: 65
Reputation: 6723
I agree with Hambone that you should take advantage of arrays. If you don't want to create an additional table, you could also do it all in one query:
select name, list,
array_to_string(ARRAY[
CASE WHEN list LIKE ANY(ARRAY['%11%', '%202%']) then 'red' else null end,
CASE WHEN list LIKE ANY(ARRAY['%36%']) then 'blue' else null end,
CASE WHEN list LIKE ANY(ARRAY['%202']) then 'green' else null end,
CASE WHEN list LIKE ANY(ARRAY['%11%', '%36%']) then 'yellow' else null end
], ', ')
from test
where list LIKE ANY(ARRAY['%11%', '%202%', '%36%', '%202%', '%11%', '%36%']);
Note that I'm using LIKE ANY instead of SIMILAR TO. You could continue to use SIMILAR TO, but it seems easier to me to use LIKE ANY.
Here's a fiddle.
Upvotes: 2
Reputation: 16387
It sounds like you really need to take advantage of PostgreSQL's most excellent array support. I feel like I say that a lot.
If you change your structure to arrays:
create table test2 (
name varchar(255),
list integer[]
)
Then, notionally you can even put your input into another table:
create table matches (
color text,
list integer[]
);
insert into matches values
('red', '{11,202}'),
('blue', '{36}'),
('green', '{202}'),
('yellow', '{11,36}');
The following query should yield the results you seek:
select
t.name, t.list, array_agg (m.color)
from
test2 t
join matches m on
m.list && t.list
group by
t.name, t.list
Upvotes: 2