Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyjsj-0002D5-SD for pgsql-sql@arkaria.postgresql.org; Wed, 08 Jun 2022 00:49:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nyjsi-0000M2-DM for pgsql-sql@arkaria.postgresql.org; Wed, 08 Jun 2022 00:49:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nyjsi-0000Lt-1d for pgsql-sql@lists.postgresql.org; Wed, 08 Jun 2022 00:49:08 +0000 Received: from mail-lj1-x230.google.com ([2a00:1450:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nyjsf-00083d-NX for pgsql-sql@lists.postgresql.org; Wed, 08 Jun 2022 00:49:07 +0000 Received: by mail-lj1-x230.google.com with SMTP id y29so21028800ljd.7 for ; Tue, 07 Jun 2022 17:49:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=BnXsLjqzU5tSllyIyXk1CIa8UO78owik32/G7GrBpus=; b=pVwOJnB60AXJCp1DfiY7NFYOXSR74RwDblldQdJhf0tEzbW7TABydhLFIPi2wrQ2HJ 8VqNAaJNhc77i9hGj60Eo2TacDoOAGC2258x0AOXfitFOsAHzhEQb0VejM6ZZJGlmLXm FUHxZV5+VMdQ+uVszFkYs0jBPCvmhWJ+2gispk/LgVnJceYF35UIZc0ItAOS9dot4YqC h86UpZfJgfWzyLgMOF2ulWsh+9uZS5SBtcd/PvNNQFHgwlkcVBXN4fI3/SZbi/LKa4m8 St8mNRcqazjG2unGiXo2wM29WNaqe9a9ZCIMHvDRm0elZOP9SnLpJEPuiqgb+h1O7lxX 4n2Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=BnXsLjqzU5tSllyIyXk1CIa8UO78owik32/G7GrBpus=; b=FMogxxwSSnnoMctZIz1PgROEJSP8Oo3SuO4RA7O08R0JDbitgJXDBrn5L1u2SCmEls 6KSqmRoNmeJoc7Dj4IS7GSPd1HPug5FE9zTBwC67JkASCfvcZRUgtiIeleSJ3hjehUJW V7OQB5WViyMHIjVB/oxE+975JaqPl2EOvM4b7S7hkUyGTRpbbKNOrqHmoMUZFpUpbRey zKUs52rxe7EbqOLsnFr0R4dkeC3UoFqMTpleQbVHamzGlccca10oXooxeRRe2Qw+30Ey peTEQK92gY370y6EhAiZg5ZYfg+yUPBIl4+Tm5Ie+1hdRZOzXJVhz9YWvF5lfkMiDUkV IU0w== X-Gm-Message-State: AOAM532kzQJVnel0EhjGTBJarxmBGI56jD9vxTah0ilRM96UeF5A2s3E B2dySBxJrw8U61W3W2DDDtTBYoSK4bXHgKhj5zxnSXfK7D8= X-Google-Smtp-Source: ABdhPJxOVCXk+EpbO9EJIWP0pRQXCRM0iiMjdAL9wOk0DMxQjW92EtUH4ucRXGoCfsTSyzVk2FcwFpdkLTvply1qWJs= X-Received: by 2002:a2e:a7d4:0:b0:255:c1aa:e2dc with SMTP id x20-20020a2ea7d4000000b00255c1aae2dcmr333600ljp.10.1654649345095; Tue, 07 Jun 2022 17:49:05 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ben Tilly Date: Tue, 7 Jun 2022 17:48:53 -0700 Message-ID: Subject: Re: losing my mind about sytnax error in window clause To: Rein Petersen Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000713b8705e0e5125c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000713b8705e0e5125c Content-Type: text/plain; charset="UTF-8" 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 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 > 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 >> >> >> >> --000000000000713b8705e0e5125c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I have a working solution to this, BUT there is enough ove= rhead in user defined functions that it didn't perform well in practice= for me.=C2=A0 Figuring out how to create temporary tables then join them c= leverly worked out better.=C2=A0 However you'd just have to rewrite you= r 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 s= ome work I was doing...

CREATE OR REPLACE FUNCTION public.last_value_when_sfunc(stat= e anyelement, value anyelement, cond BOOL) RETURNS anyelement AS
$$
= =C2=A0 =C2=A0 SELECT CASE WHEN cond THEN value ELSE state END;
$$
LAN= GUAGE SQL;

COMMENT ON FUNCTION public.last_value_when_sfunc (anyelem= ent, anyelement, bool) IS 'Helper function for tracking last matching i= n window';

DO
$$
BEGIN
=C2=A0 =C2=A0 IF 0 < (SELECT = count(*)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM pg_proc p
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 LEFT JOIN pg_namespace= n
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= ON p.pronamespace =3D n.oid
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 W= HERE n.nspname =3D 'public' AND p.proname =3D 'last_value_when&= #39;)
=C2=A0 =C2=A0 THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 DROP AGGREGATE p= ublic.last_value_when(anyelement, bool);
=C2=A0 =C2=A0 END IF;
END$$
language 'plpgsql';

CREATE AGGREGATE public.last_value_when(anyelement, bool) (=C2=A0 =C2=A0 SFUNC =3D public.last_value_when_sfunc,
=C2=A0 =C2=A0 ST= YPE =3D anyelement);

COMMENT ON AGGREGATE public.last_value_when (an= yelement, 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@rei= npetersen.com> wrote:
Fixing the typo (*PARTITION) but bump in= to=C2=A0ERROR: =C2=A0FILTER is not implemented for non-aggregate window fun= ctions ....=C2=A0

What Im really trying to do is merge rows into a s= ingle row, allowing the later rows to take precedence. I thought=C2=A0it cr= acked...=C2=A0

On Tue, Jun 7, 2022 at 6:03 PM Rein Petersen <email@reinpetersen.com> wrote:
Hi Folks,

I've been reading the do= cs over and again, searching exhaustively for examples on the internet to h= elp me here and I'm ready to give up.

I have a= query using windows function:

SELECT
=C2=A0 =C2=A0 last= _value ( typechange ) =C2=A0 =C2=A0 =C2=A0OVER w,
=C2=A0 =C2=A0 p= index,
=C2=A0 =C2=A0 lid,
=C2=A0 =C2=A0 last_value ( mo= dified ) =C2=A0 OVER w,
=C2=A0 =C2=A0 last_value ( created ) =C2= =A0 =C2=A0OVER w,
=C2=A0 =C2=A0 last_value ( modifiedby ) OVER w,=
=C2=A0 =C2=A0 last_value ( createby ) =C2=A0 OVER w,
= =C2=A0 =C2=A0 last_value ( cost ) FILTER ( WHERE cost IS NOT NULL ) OVER w,=
=C2=A0 =C2=A0 last_value ( sell ) FILTER ( WHERE sell IS= NOT NULL ) OVER w
FROM ps._delta_ext
=
WHERE pindex =3D Ppindex = AND
=C2=A0 =C2=A0 ( Plid IS= NULL OR lid =3D Plid )= AND
=C2=A0 =C2=A0 ( Ptimestamp IS NULL OR modifie= d <=3D Ptimestamp )
GROU= P BY lid, pindex
WINDOW w AS ( PARITION BY lid, pindex ORDER BY created );
Complains about synta= x error in the WINDOW cause (last line) on [PARTITION] BY:
/* mess= ages
ERROR: =C2=A0sy= ntax error at or near "BY"
LINE 16: =C2=A0WINDOW w AS ( PARITION BY locationid, parti= ndex ORDER BY cr...
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
SQL state: 42601
Character: 2724
*/

Maybe my= error is clear to someone else, could really use helping hand, thanks.

Rein



--000000000000713b8705e0e5125c--