From: | Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Possible bug: SQL function parameter in window frame definition |
Date: | 2019-09-28 15:33:50 |
Message-ID: | DB6PR0202MB2904E7FDDA9D81504D1E8C68E3800@DB6PR0202MB2904.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2019-09-28 15:59:55 | Re: Possible bug: SQL function parameter in window frame definition |
Previous Message | Sonam Sharma | 2019-09-28 13:44:26 | Re: Pg_auto_failover |
From | Date | Subject | |
---|---|---|---|
Next Message | David Steele | 2019-09-28 15:51:29 | Re: Standby accepts recovery_target_timeline setting? |
Previous Message | Justin Pryzby | 2019-09-28 15:18:00 | default partitions can be partitioned and have default partitions? |