主页 / dba / 问题 / 286943
Accepted
Hedi Hadhri
Asked: 2021-03-13 23:35:36 +0800 CST

postgres函数的问题

  • 772

我编写了以下函数来从我作为参数提供的表和列中查询数据：

create or replace function field_summaries(gap interval , _tbl anyelement, _col text)                                
 returns SETOF anyelement as 
 $func$   
 BEGIN
 RETURN QUERY EXECUTE
 'select                                                                                                                       
 time_bucket(' || gap || ', time)::text as hour,                                                                                      
 avg(' || _col ||'),                                                                                                          
 min(' || _col ||'),                                                                                                          
 max(' || _col ||')                                                                                                         
 from ' || pg_typeof(_tbl) || ' d                                                                                                         
 where d.device_id in (                                                                                                       
     select device_id from sensors)                                                                                           
     group by hour';
 END
 $func$ language sql stable;

问题是当调用这样的函数时：

select field_summaries('5minutes', NULL:: m_13, 'temperature');

我收到以下错误：

ERROR:  syntax error at or near ":"  
LINE 2:  time_bucket(00:05:00, time)::text as hour,                 ...                                                                           
                       ^                                                                                                   
QUERY:  select                                                                                                                                                                                                                                         
time_bucket(00:05:00, time)::text as hour,                                                                                                                                                                                                            
avg(temperature),                                                                                                                                                                                                                                     
min(temperature),                                                                                                                                                                                                                                     
max(temperature)                                                                                                           
from m_13 d                                                                                                                
where d.device_id in (                                                                                                                                                                                                                                    
select device_id from sensors)                                                                                                                                                                                                                        
group by hour                                                                                                         
CONTEXT:  PL/pgSQL function field_summaries(interval,anyelement,text) line 3 at RETURN QUERY

有谁知道这可能是什么？

postgresql plpgsql

2 个回答

  1. gap将数据类型从intervalto更改为text并将其括在动态查询中的单引号之间。

    create function test (gap text)
returns text
as $ftest$
declare
    cmd text;
begin
    cmd := 'select ''' ||  gap || '''::interval';
    return cmd;
end
$ftest$ language plpgsql 

select test('5 minute');

test                        
:--------------------------
select '5 minute'::interval

    select '5 minute'::interval

| interval |
| :------- |
| 00:05:00 |

    db<>在这里摆弄

    • 1
  2. Best Answer

    错误的原因是您只是将参数连接到字符串中。使用时EXECUTE，参数应$1, $2, ...在字符串中使用 using 表示，值应与命令的USING子句一起传递EXECUTE。

    此外，应该使用format()函数创建动态 SQL。一方面是因为它可以正确处理标识符（使用%I占位符）。它还使 SQL 更易于阅读。

    由于列及其数据类型是固定的，我还将函数更改为，returns table(...)因为以后更容易使用。

    所以函数应该是这样的：

    create or replace function field_summaries(gap interval , _tbl anyelement, _col text)                                
  returns table(hour text, average numeric, minimum numeric, maximum numeric) 
$func$   
begin
 RETURN QUERY 
  EXECUTE format('select time_bucket($1, time)::text as hour,                                                                                      
                         avg(%I), 
                         min(%I),
                         max(%I)                                                                                                         
                  from %I d                                                                                                         
                  where d.device_id in (select device_id from sensors)                                                                                           
                  group by hour', _col, _col, _col, _tbl)
     USING gap; --<< this passes the parameter to the EXECUTE command
END;
$func$ 
language plpgsql
stable;

    您将需要调整returns table()部件中输出列的数据类型。

    • 0

相关问题