Thread: Sample of user-define window function and other things

Attached is a simple user-define window function as a test, which
calculates moving avg(). Writing this, I found that even with current
specification (i.e. limited frame clauses), user-define function can
emulate some kinds of moving frame for simple purpose.

CREATE OR REPLACE FUNCTION movavg(float8, int4) RETURNS float8 AS
'$libdir/moving', 'movavg'
LANGUAGE 'c' WINDOW;

SELECT depname, salary, movavg(salary::float8, 1) OVER (
    PARTITION BY depname
    ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
FROM empsalary;


And miscellaneous questions:
- CREATE FUNCTION command accepts WINDOW keyword for non-c language
like plpgsql. Don't we need to throw error?
- Is WinGetFuncArgInPartition()'s argument mark_pos required? For the
newbies to window functions, it seems a bit confusing, but
WinSetMarkPos() looks enough for the purpose AFAIK.


Regards,

--
Hitoshi Harada

"Hitoshi Harada" <umi.tanuki@gmail.com> writes:
> - CREATE FUNCTION command accepts WINDOW keyword for non-c language
> like plpgsql. Don't we need to throw error?

No.  CREATE FUNCTION has no business trying to keep track of which
PLs implement what.  That case won't do anything useful right now,
but it might do something useful sometime before 8.4 is dead ...

> - Is WinGetFuncArgInPartition()'s argument mark_pos required? For the
> newbies to window functions, it seems a bit confusing, but
> WinSetMarkPos() looks enough for the purpose AFAIK.

You mean set_mark?  It's just to save an extra calculation of the
absolute location of the fetched row.  See leadlag_common for an
example use: we can truncate the tuplestore if the offset is constant.
        regards, tom lane

2009/1/8 Tom Lane <tgl@sss.pgh.pa.us>:
> "Hitoshi Harada" <umi.tanuki@gmail.com> writes:
>> - Is WinGetFuncArgInPartition()'s argument mark_pos required? For the
>> newbies to window functions, it seems a bit confusing, but
>> WinSetMarkPos() looks enough for the purpose AFAIK.
>
> You mean set_mark?  It's just to save an extra calculation of the
> absolute location of the fetched row.  See leadlag_common for an
> example use: we can truncate the tuplestore if the offset is constant.

Yeah, I mean set_mark :P) I already checked leadlag_common example but
abs_pos can be very simply calculated by WinGetCurrentPosition() -
offset. So the simpler API is winner to me rather than saving few
cycle cpu cost.


Regards,

-- 
Hitoshi Harada

"Hitoshi Harada" <umi.tanuki@gmail.com> writes:
> 2009/1/8 Tom Lane <tgl@sss.pgh.pa.us>:
>> You mean set_mark?  It's just to save an extra calculation of the
>> absolute location of the fetched row.  See leadlag_common for an
>> example use: we can truncate the tuplestore if the offset is constant.

> Yeah, I mean set_mark :P) I already checked leadlag_common example but
> abs_pos can be very simply calculated by WinGetCurrentPosition() -
> offset. So the simpler API is winner to me rather than saving few
> cycle cpu cost.

In this particular case it's not hard, but in the general case where you
are using any of the seekpos values it could require a significant
amount of code.  More to the point: the available examples suggest
to me that truncating at the row you fetch is likely to be a pretty
common behavior; in fact *every one* of the existing callers of
WinGetFuncArgInPartition or WinGetFuncArgInFrame does that, at least
conditionally.  So I think it's reasonable to have an extra parameter
instead of making a lot of callers reinvent the same wheel.
        regards, tom lane

Hitoshi Harada wrote:
> - CREATE FUNCTION command accepts WINDOW keyword for non-c language
> like plpgsql. Don't we need to throw error?

The validator procedures of those languages should be made to reject 
that case if they can't support it.

Peter Eisentraut <peter_e@gmx.net> writes:

> Hitoshi Harada wrote:
>> - CREATE FUNCTION command accepts WINDOW keyword for non-c language
>> like plpgsql. Don't we need to throw error?
>
> The validator procedures of those languages should be made to reject that case
> if they can't support it.

Even if they can support it shouldn't they reject functions that aren't
actually window functions? What happens if you mark a perfectly normal
function as a window function, does it behave sanely?

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
 

Gregory Stark <stark@enterprisedb.com> writes:
> Even if they can support it shouldn't they reject functions that aren't
> actually window functions? What happens if you mark a perfectly normal
> function as a window function, does it behave sanely?

Yes, for small values of "sane".  It will see all its arguments as NULL
(and will get called even if it was marked STRICT).  Whatever it returns
under those circumstances is what the result will be.

I do not offhand see a reasonable interpretation for the combination of
WINDOW and STRICT attributes, so perhaps it'd be sensible for CREATE
FUNCTION to throw an error for that.
        regards, tom lane