将列表传递给where子句 [英] passing a list to a where clause
问题描述
我正在尝试从SQL导出到.csv,如果我对其进行硬编码以接受一定数量的参数,则它可以工作。问题是,我想允许用户请求任意数量的参数,并将它们传递给where子句。代码应该使它更清晰。
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'
所以我想我要做的是将列表传递给where子句,而不是显式的:dates#变量。例如,一个人可以使用参数 2012-01-04 12:00、2012-02-04 12:00、2012-03-04 12:00或两个或一个参数来运行脚本。对于三个字符串,该字符串将解析为 2012-01-04 12:00, 2012-02-04 12:00, 2012-03-04 12:00。
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'.
我已经尝试过string_to_array(),unnest(regexp_matches(:dates,expression))和regexp_split_to_table(:dates,expression),尽管我不确定如何进行连接。我尝试过的各种解决方案都产生了许多错误,包括:
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:
无法将类型text []转换为没有时区的时间戳
cannot cast type text[] to timestamp without time zone
无法将类型记录转换为没有时区的时间戳
cannot cast type record to timestamp without time zone
regexp_split不支持全局选项
regexp_split does not support the global option
WHERE的参数不能返回一个集合
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!
推荐答案
尝试一下:
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');
查询:
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
实时测试: http://www.sqlfiddle.com/#!1/43d48/26
您也可以使用IN,只是将数组嵌套到行:
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))
实时测试: 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[] ))
但是我犹豫不决,因为这会在stackoverflow上引起水平滚动条:-)
But I hesitates to do so, as it causes horizontal scrollbar here on stackoverflow :-)
实时测试: http://www.sqlfiddle.com/#!1/43d48/31
这篇关于将列表传递给where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!