PostgreSQL: Pass value from an array and do union

Question

WITH temp AS (select * from t1 where c1 = 'string1')
select 'string1' as col1, t2.col2, temp.col3 from t2 inner join temp on t2.c2 = temp.c2 where t2.some_col like 'string1%'
union
WITH temp AS (select * from t1 where c1 = 'string2')
select 'string2' as col1, t2.col2, temp.col3 from t2 inner join temp on t2.c2 = temp.c2 where t2.some_col like 'string2%'
...

Above is just an example of a PostgreSQL query I am trying to run. It's a union of two completely similar queries. They only use different values for matching string1 and string2.

I have about 20 such queries that I want to do a union on. They only differ by the variable I want to use for comparison such as string1

How can I use such array of values ['string1', 'string2', 'string3', .., 'string20'], run a query on each variable from this array and union them?

Michel Milezzi · Accepted Answer · 2017-05-19 02:14:00Z

What about a old fashioned plpgsql?

CREATE OR REPLACE FUNCTION get_all_foo(arr varchar[]) RETURNS TABLE (col1 TEXT, col2 TEXT, col3 TEXT) AS
$BODY$
DECLARE
   arr_value varchar;
   generated_query varchar := '';
   array_index smallint := 1;
   array_length smallint;
BEGIN
        array_length := array_length(arr, 1);        
        FOREACH arr_value IN ARRAY arr LOOP

                generated_query := generated_query || format(' (WITH temp AS (select * from t1 where c1 = %L) '
                   'select %L as col1, t2.col2, temp.col3 from t2 inner join temp on t2.c2 = temp.c2 where t2.some_col like ''%s%%'')', arr_value, arr_value, arr_value);

                IF array_index < array_length THEN
                        generated_query := generated_query || ' UNION ';
                END IF;

                array_index := array_index+1;

        END LOOP;

        RAISE DEBUG 'Generated query: %', generated_query;

        RETURN QUERY EXECUTE generated_query;

END
$BODY$
LANGUAGE plpgsql;

--Uncomment to see generated query
--SET client_min_messages = DEBUG;
SELECT * FROM get_all_foo(array['string1', 'string2', 'string3', 'string4', 'string5']);

cur4so · Accepted Answer · 2017-05-19 01:52:25Z

0

select c1 as col1, t2.col2, temp.col3 
from 
(select col2, c2 from t2 where 
some_col like 'string1%' or some_col like 'string2%' or <other strings in the similar fashion>) t2 
inner join
(select c1,c2,col3 from t1 where c1 in ('string1', 'string2', <other strings in the similar fashion>)) temp 
 on t2.c2 = temp.c2;

answered May 19, 2017 at 1:52

cur4so

1,8204 gold badges21 silver badges29 bronze badges

Comments

Abelisto · Accepted Answer · 2017-05-19 06:36:45Z

0

WITH temp AS (
  select *
  from t1
  where c1 = any(array['string1','string2','string3']))
select distinct
  temp.c1 as col1, t2.col2, temp.col3
from t2 inner join
  temp on (t2.c2 = temp.c2 and t2.some_col like temp.c1||'%')

answered May 19, 2017 at 6:36

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Pass value from an array and do union

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related