From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | poc - possibility to write window function in PL languages |
Date: | 2020-08-24 16:08:06 |
Message-ID: | CAFj8pRCXDO2OiF5=0Y6QMeHobXu5apvX2LJg10+VHC=O5dGvaQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
I wrote a proof concept for the support window function from plpgsql.
Window function API - functions named WinFuncArg* are polymorphic and it is
not easy to wrap these functions for usage from SQL level. I wrote an
enhancement of the GET statement - for this case GET WINDOW_CONTEXT, that
allows safe and fast access to the result of these functions.
Custom variant of row_number can look like:
create or replace function pl_row_number()
returns bigint as $$
declare pos int8;
begin
pos := get_current_position(windowobject);
pos := pos + 1;
perform set_mark_position(windowobject, pos);
return pos;
end
$$
language plpgsql window;
Custom variant of lag function can look like:
create or replace function pl_lag(numeric)
returns numeric as $$
declare
v numeric;
begin
perform get_input_value_in_partition(windowobject, 1, -1, 'seek_current',
false);
get pg_window_context v = PG_INPUT_VALUE;
return v;
end;
$$ language plpgsql window;
Custom window functions can be used for generating missing data in time
series
create table test_missing_values(id int, v integer);
insert into test_missing_values
values(1,10),(2,11),(3,12),(4,null),(5,null),(6,15),(7,16);
create or replace function pl_pcontext_test(numeric)
returns numeric as $$
declare
n numeric;
v numeric;
begin
perform get_input_value_for_row(windowobject, 1);
get pg_window_context v = PG_INPUT_VALUE;
if v is null then
v := get_partition_context_value(windowobject, null::numeric);
else
perform set_partition_context_value(windowobject, v);
end if;
return v;
end
$$
language plpgsql window;
select id, v, pl_pcontext_test(v) over (order by id) from
test_missing_values;
id | v | pl_pcontext_test.
----+----+------------------
1 | 10 | 10
2 | 11 | 11
3 | 12 | 12
4 | | 12
5 | | 12
6 | 15 | 15
7 | 16 | 16
(7 rows)
I think about another variant for WinFuncArg functions where polymorphic
argument is used similarly like in get_partition_context_value - this patch
is prototype, but it works and I think so support of custom window
functions in PL languages is possible and probably useful.
Comments, notes, ideas, objections?
Regards
Pavel
Attachment | Content-Type | Size |
---|---|---|
plpgsql-window-function-support.patch | text/x-patch | 43.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2020-08-24 16:10:49 | Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions |
Previous Message | Mark Dilger | 2020-08-24 15:04:00 | Re: factorial function/phase out postfix operators? |