Início / dba / Perguntas / 286943
Accepted
Hedi Hadhri
Asked: 2021-03-13 23:35:36 +0800 CST

Problema com a função postgres

  • 772

Eu escrevi a seguinte função para consultar dados de tabelas e colunas que dou como argumentos:

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;

O problema é que ao chamar a função assim:

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

Eu recebo o seguinte erro:

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

Alguém tem ideia do que pode ser isso?

postgresql plpgsql

2 respostas

  1. Altere o gaptipo de dados de intervalpara texte coloque-o entre aspas simples em sua consulta dinâmica.

    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<>fique aqui

    • 1
  2. Best Answer

    O motivo do seu erro é o fato de você apenas concatenar o parâmetro na string. Ao usar EXECUTE, os parâmetros devem ser indicados usando $1, $2, ...dentro da string e os valores devem ser passados ​​com a USINGcláusula do EXECUTEcomando.

    Além disso, SQL dinâmico deve ser criado usando a função format() . Por um lado, porque ele pode lidar adequadamente com identificadores (usando o %Iespaço reservado). E torna o SQL mais fácil de ler também.

    Como as colunas e seus tipos de dados são fixos, eu também mudaria a função para returns table(...)porque é mais fácil de usar depois.

    Então a função deve ficar assim:

    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;

    Você precisará ajustar os tipos de dados das colunas de saída na returns table()peça.

    • 0

relate perguntas