public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ben Tilly <[email protected]>
To: Rein Petersen <[email protected]>
Cc: [email protected]
Subject: Re: losing my mind about sytnax error in window clause
Date: Tue, 7 Jun 2022 17:48:53 -0700
Message-ID: <CANoac9VZ4T4L8jhH6XuZBe5EmR11jrcdXc49FOBtYLdOp1j1qw@mail.gmail.com> (raw)
In-Reply-To: <CAMj5WPTmuhUFkUb6N5Efdg0oP4Y_y4xJrfUtK+SoiPDD+u4RMQ@mail.gmail.com>
References: <CAMj5WPQDan-kuny1GGC5GWFt6jcOyrSbQHd+g0Ga15vqs8XjbA@mail.gmail.com>
	<CAMj5WPTmuhUFkUb6N5Efdg0oP4Y_y4xJrfUtK+SoiPDD+u4RMQ@mail.gmail.com>

I have a working solution to this, BUT there is enough overhead in user
defined functions that it didn't perform well in practice for me.  Figuring
out how to create temporary tables then join them cleverly worked out
better.  However you'd just have to rewrite your FILTER lines as something
like last_value_when( cost, cost is not null ) over w.

Pity, because if this performed well, it would be really convenient for
some work I was doing...

CREATE OR REPLACE FUNCTION public.last_value_when_sfunc(state anyelement,
value anyelement, cond BOOL) RETURNS anyelement AS
$$
    SELECT CASE WHEN cond THEN value ELSE state END;
$$
LANGUAGE SQL;

COMMENT ON FUNCTION public.last_value_when_sfunc (anyelement, anyelement,
bool) IS 'Helper function for tracking last matching in window';

DO
$$
BEGIN
    IF 0 < (SELECT count(*)
            FROM pg_proc p
                LEFT JOIN pg_namespace n
                    ON p.pronamespace = n.oid
            WHERE n.nspname = 'public' AND p.proname = 'last_value_when')
    THEN
        DROP AGGREGATE public.last_value_when(anyelement, bool);
    END IF;
END
$$
language 'plpgsql';

CREATE AGGREGATE public.last_value_when(anyelement, bool) (
    SFUNC = public.last_value_when_sfunc,
    STYPE = anyelement);

COMMENT ON AGGREGATE public.last_value_when (anyelement, bool) IS
'Aggregate function for tracking the last value when a condition was true';

On Tue, Jun 7, 2022 at 5:14 PM Rein Petersen <[email protected]> wrote:

> Fixing the typo (*PARTITION) but bump into ERROR:  FILTER is not
> implemented for non-aggregate window functions ....
>
> What Im really trying to do is merge rows into a single row, allowing the
> later rows to take precedence. I thought it cracked...
>
> On Tue, Jun 7, 2022 at 6:03 PM Rein Petersen <[email protected]>
> wrote:
>
>> Hi Folks,
>>
>> I've been reading the docs over and again, searching exhaustively for
>> examples on the internet to help me here and I'm ready to give up.
>>
>> I have a query using windows function:
>>
>> SELECT
>>     last_value ( typechange )      OVER w,
>>     pindex,
>>     lid,
>>     last_value ( modified )   OVER w,
>>     last_value ( created )    OVER w,
>>     last_value ( modifiedby ) OVER w,
>>     last_value ( createby )   OVER w,
>>     last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,
>>     last_value ( sell ) FILTER ( WHERE sell IS NOT NULL ) OVER w
>> FROM ps._delta_ext
>> WHERE pindex = Ppindex AND
>>     ( Plid IS NULL OR lid = Plid ) AND
>>     ( Ptimestamp IS NULL OR modified <= Ptimestamp )
>> GROUP BY lid, pindex
>> WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );
>> Complains about syntax error in the WINDOW cause (last line) on
>> [PARTITION] BY:
>> /* messages
>> ERROR:  syntax error at or near "BY"
>> LINE 16:  WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...
>>                                  ^
>> SQL state: 42601
>> Character: 2724
>> */
>>
>> Maybe my error is clear to someone else, could really use helping hand,
>> thanks.
>>
>> Rein
>>
>>
>>
>>

view thread (6+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: losing my mind about sytnax error in window clause
  In-Reply-To: <CANoac9VZ4T4L8jhH6XuZBe5EmR11jrcdXc49FOBtYLdOp1j1qw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox