PostgreSQL 函数时间戳参数

Question

这是我的第一个函数。 选择查询在使用固定常量手动执行时效果很好。 但是在我的功能中，参数被解释为。

基本上，我正在尝试用函数参数替换时间戳。

-- this my mananual query.  it works fine.
select  sum(least(end_time,'2017-05-16 11:30:00')- greatest(start_time,'2017-05-16 10:30:00')) as duration,
    from c_trunk 
    where (start_time < '2017-05-16 11:30:00') and (end_time > '2017-05-16 10:30:00')
    and trunk < 20
-- this is my bogus function with the select that calls it at the end. 
create function trunk_trafficD (p1 timestamp, p2 timestamp)
    returns boolean as $$
begin
select  sum(least(end_time,p2)- greatest(start_time,p1)) as duration,
    count(call_id), device as "system", trunk as "trunk" 
    from c_trunk 
    where (start_time < p2) and (end_time > p1)
    and trunk < 20
    and (device = 1 or device = 4 or device = 3)
return true;
end; $$
language PLPGSQL;
select trunk_trafficD('2017-05-16 10:30:00','2017-05-16 11:30:00');

Answer 1

您没有显示错误消息，但在 PL/pgSQL 中您必须使用

SELECT expr1, expr2, ...
  INTO var1, var2, ...
FROM ...

或者，如果要丢弃结果，请使用PERFORM而不是SELECT。

Answer 2

您需要对参数使用 to_timestamp(( finction((