Problem with postgres Function

Question

I wrote the following function to query data from tables and columns that I give as arguments:

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; 

The problem is that when call the function like this:

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

I receive the following error:

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

Does anyone have an idea of what this could be?

• Hedi Hadhri: sorry it's func$ language plpgsql
• McNets: Hi, welcome to the forum. Please tag your PostgreSQL version. Is seems related to TimescaleDB, change gap datatype from interval to text enclosed in a single quotes.

Answer 1

The reason for your error is the fact that you just concatenate the parameter into the string. When using EXECUTE, parameters should be denoted using 1,ドル 2,ドル ... inside the string and the values should be passed with the USING clause of the EXECUTE command.

Additionally, dynamic SQL should be created using the format() function. For one because it can properly deal with identifiers (using the %I placeholder). And it makes the SQL easier to read as well.

As the columns and their data types are fixed, I would also change the function to returns table(...) because that is easier to use later.

So the function should look something like this:

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;

You will need to adjust the data types of the output columns in the returns table() part.

• Hedi Hadhri: Hi, thanks for your answer. I have another question: Could I call the function using a text as the table name instead of NULL::tablename ? I need to enter the name as string when I call the function but when I do it it says table does not exist. Thanks in advance.
• user1822: Sure. That's why using format() is recommended.
• Hedi Hadhri: It seems to not work =/ For example when I call the function like this 'select fied_summaries('5minutes', NULL:: m_13, 'current')' it executes but when I do 'select fied_summaries('5minutes', 'm_13', 'current')' it throws an error that m_13 does not exist. My goal is to call the function like that. Is there away to make the NULL::m_13 call inside the function?

Answer 2

Change gap datatype from interval to text and enclose it between single quotes in your dynamic query.

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<>fiddle here