我正在尝试从SQL导出到.csv,如果我对其进行硬编码以接受一定数量的参数,则它可以正常工作。问题是,我想允许用户请求任意数量的参数,并将它们传递给where子句。该代码应该使这一点更加清楚。
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-0412: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”。
我已经尝试过string_to_array(),unnest(regexp_matches(:dates,expression))和regexp_split_to_table(:dates,expression),尽管我不确定如何进行连接。我尝试过的各种解决方案都产生了许多错误,包括:
无法将类型text []转换为没有时区的时间戳
无法将类型记录转换为没有时区的时间戳
regexp_split不支持全局选项
WHERE的参数不能返回集合
最后一个特别令人沮丧,我很茫然,不胜感激。有一种更简单的方法可以执行此操作,不是吗?谢谢!
试试这个:
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,只是将数组嵌套到行:
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
您也可以将它们全部放在一行中:
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上引起水平滚动条:-)
实时测试:http://www.sqlfiddle.com/#!1 / 43d48 / 31