passing a list to a where clause

Question

I am trying to export from SQL to .csv and it works if I hard code it to accept a certain number of arguments. The things is, I want to allow the user to request any number of arguments and have these be passed to the where clause. The code should make this a bit more clear.

create temporary table bdates as

 select tt.date, tt.time, tt.location
 from birthdays as bd
 inner join days as d
   on (d.id = bd.birth_id)
 inner join total_time as tt
   on (bd.date = tt.date and
       bd.time = tt.time and
       d.day_of = tt.location)
 where tt.date in(:date1, :date2) --defined by user at command line
 order by...

\copy bdates to '.csv'

So what I think I want to do is pass a list to that where clause instead of explicit :dates# variables. For example, a person could run the script with the argument '2012-01-04 12:00, 2012-02-04 12:00, 2012-03-04 12:00', or just two arguments or one. In the case of three the string would be parsed to '2012-01-04 12:00', '2012-02-04 12:00', '2012-03-04 12:00'.

I've tried string_to_array(), unnest(regexp_matches(:dates, expression)) and regexp_split_to_table(:dates, expression), though I'm not sure how to do the join. the various solutions I've tried have produced numerous errors including:

cannot cast type text[] to timestamp without time zone

cannot cast type record to timestamp without time zone

regexp_split does not support the global option

argument of WHERE must not return a set

The last one is especially disheartening and I'm at a loss and would appreciate any input. There's a simpler way to do this, isn't there? Thanks!

Answer 1

Try this:

create table x(d timestamp);

insert into x values
('jan 2, 2012'),
('february 4, 2012 12:00'),
('jan 4, 2012 12:00'),
('march 1, 2012'),
('may 3, 2012');

Query:

with input as
(
  select 
  '2012-1-2, 2012-01-04 12:00, 2012-02-04 12:00, 2012-03-04 12:00'::text
  as d_input
)
,converted_to_array as
(
  select ('{' || d_input || '}')::timestamp[] as d_array
  from input 
)
select d
from x cross join converted_to_array
where d = any(d_array)

Output:

D
January, 02 2012 00:00:00-0800
February, 04 2012 12:00:00-0800
January, 04 2012 12:00:00-0800

Live test: http://www.sqlfiddle.com/#!1/43d48/26

---

You can also use IN, just unnest array to rows:

with input as
(
  select 
  '2012-1-2, 2012-01-04 12:00, 2012-02-04 12:00, 2012-03-04 12:00'::text
  as d_input
)
,converted_to_array as
(
  select ('{' || d_input || '}')::timestamp[] as d_array
  from input 
)
select d
from x cross join converted_to_array
where d in (select unnest(d_array))

Live test: http://www.sqlfiddle.com/#!1/43d48/29

---

You can put them all in one line too:

select d
from x 
where d in (select unnest( ('{' || '2012-1-2, 2012-01-04 12:00, 2012-02-04 12:00, 2012-03-04 12:00'::text || '}')::timestamp[] ))

But I hesitates to do so, as it causes horizontal scrollbar here on stackoverflow :-)

Live test: http://www.sqlfiddle.com/#!1/43d48/31

Answer 2

You almost had the simplest and fastest method:

Test table:

CREATE TEMP TABLE t(d date);
INSERT INTO t VALUES
 ('2012-1-1')
,('2012-1-4')
,('2012-2-2')
,('2012-2-4')
,('2012-3-3')
,('2012-3-4');

As you are filtering on dates, not timestamps, you can simplify your example input:

'2012-01-04 12:00, 2012-02-04 12:00, 2012-03-04 12:00'

to:

'2012-01-04, 2012-02-04, 2012-03-04'

Query (works with either input):

SELECT t.*
FROM   t
JOIN  (SELECT unnest(string_to_array(
                    '2012-01-04, 2012-02-04, 2012-03-04', ', ')::date[]) AS d
    ) x USING (d)

Could also be done with:

       SELECT regexp_split_to_table(
                       '2012-01-04, 2012-02-04, 2012-03-04', ', ')::date AS d

But regexp_split_to_table() is slower.
Generally, a JOIN is faster to select rows than the relatively slow IN().