Hi all,
I noticed this strange behaviour whilst trying to write a function for Postgres 11.5 (PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit) and reduced it to this minimal example. Using a function parameter in the window frame definition seems to be the cause of the error.
create or replace function f(group_size bigint) returns setof int[] as
$$
select array_agg(s) over w
from generate_series(1,10) s
window w as (order by s rows between current row and group_size following)
$$ language sql immutable;
Calling the function without a column list succeeds:
postgres=# select f(3);
f
------------
{1,2,3,4}
{2,3,4,5}
{3,4,5,6}
{4,5,6,7}
{5,6,7,8}
{6,7,8,9}
{7,8,9,10}
{8,9,10}
{9,10}
{10}
(10 rows)
Calling the function with select * fails:
postgres=# select * from f(3);
ERROR: 42704: no value found for parameter 1
LOCATION: ExecEvalParamExtern, execExprInterp.c:2296
Using a plpgsql function with a stringified query works, which is my current workaround:
create or replace function f1(group_size bigint) returns setof int[] as
$$
begin
return query execute format($q$
select array_agg(s) over w as t
from generate_series(1,10) s
window w as (order by s rows between current row and %1$s following)
$q$,group_size);
end;
$$ language plpgsql immutable;
This appears to be a bug to me. If confirmed that this is not some expected behaviour unknown to me I will report this.
Alastair
