Re: losing my mind about sytnax error in window clause - Mailing list pgsql-sql
| From | Ben Tilly |
|---|---|
| Subject | Re: losing my mind about sytnax error in window clause |
| Date | |
| Msg-id | CANoac9VZ4T4L8jhH6XuZBe5EmR11jrcdXc49FOBtYLdOp1j1qw@mail.gmail.com Whole thread Raw |
| In response to | Re: losing my mind about sytnax error in window clause (Rein Petersen <email@reinpetersen.com>) |
| List | pgsql-sql |
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';
$$
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';
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@reinpetersen.com> 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@reinpetersen.com> 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:SELECTlast_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 wFROM ps._delta_extWHERE pindex = Ppindex AND( Plid IS NULL OR lid = Plid ) AND( Ptimestamp IS NULL OR modified <= Ptimestamp )GROUP BY lid, pindexWINDOW w AS ( PARITION BY lid, pindex ORDER BY created );Complains about syntax error in the WINDOW cause (last line) on [PARTITION] BY:/* messagesERROR: syntax error at or near "BY"LINE 16: WINDOW w AS ( PARITION BY locationid, partindex ORDER BY cr...^SQL state: 42601Character: 2724*/Maybe my error is clear to someone else, could really use helping hand, thanks.Rein