Subject: losing my mind about sytnax error in window clause

From: Rein Petersen <[email-masked]>
Date: Tue, 7 Jun 2022 18:03:42 -0500

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 07 Jun 2022 19:40:26 -0400

> LINE 16:  WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...

If you really spelled it like that, "PARTITION BY" should work better.

			regards, tom lane

---

From: Rob Sargent <[email-masked]>
Date: Tue, 7 Jun 2022 19:45:24 -0400

> On Jun 7, 2022, at 7:03 PM, Rein Petersen <[email-masked]> 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.
> 
> Complains about syntax error in the WINDOW cause (last line) on [PARTITION] BY:
> /* messages
> Maybe my error is clear to someone else, could really use helping hand, thanks.
> 
> Rein
> 
> 
Why doesn’t your colorizer pick up ‘window’?  Perhaps it doesn’t pickup ‘partition' either (when properly spelled)?

---

From: Bob Edwards <[email-masked]>
Date: Wed, 8 Jun 2022 09:46:18 +1000

> Maybe my error is clear to someone else, could really use helping hand, 

PARITION <> PARTITION - check your spelling.

cheers,
Bob Edwards.

---

From: Rein Petersen <[email-masked]>
Date: Tue, 7 Jun 2022 19:14:32 -0500

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...

---

From: Ben Tilly <[email-masked]>
Date: Tue, 7 Jun 2022 17:48:53 -0700

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';