Geek asks and answers

Passing a list to a where clause

I am trying to export from SQL to .csv and it works if I hardcode it to accept a certain number of arguments. The thing is, I want to allow the user to request any number of arguments and pass them to the where clause. The code should make this clearer.

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 the list to this where clause instead of explicit: date # variables. For example, a person can run a 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 lines, "2012-01-04 12:00", "2012-02-04 12:00", "2012-03-04 12:00" will be processed.

string_to_array(), unsest (regexp_matches (: , )) regexp_split_to_table (: , ), , . , , , :

[]

regexp_split

WHERE

, . , ? !

+3
2

:

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)

:

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

IN, :

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

:

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[] ))

, stackoverflow: -)

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

+2

:

:

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');

, , :

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


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

( ):

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)

:

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

regexp_split_to_table() .
, JOIN , IN().

+1

More articles:


All Articles