MIME-Version: 1.0
References: <CA+FnnTyWEsYi=_QTeSXoGXd7Fkd8C7_AnCc5=hho-DZF9_iunQ@mail.gmail.com>
 <90741d5f-5cda-4c4a-84f4-c470129732f4@aklaver.com> <CA+FnnTz9-cjBDf3mC9fx_FCtZGDTneF9=w8LpTRdwCfxHN86yw@mail.gmail.com>
 <CAKFQuwZGjMYbP0nWYb2+-D1a_dNg6b-mDhDC96Am-c2MZOYTNw@mail.gmail.com>
 <CA+FnnTy2Lk+K8t2uKwc1cAnCm6+RTb72eVRxt9+Y8y+C=sUeyA@mail.gmail.com>
 <473602.1742698901@sss.pgh.pa.us> <CA+FnnTztyXpMxEiKTZhxMtcQ8Nm6D+M1jbfJFfkJYU9dyvTeLg@mail.gmail.com>
 <CAKFQuwZ3-wmpyUEdHUaL7H9RHp-FTxUJjVL3L5A0jqA2bbHXYw@mail.gmail.com>
 <CAKFQuwb6vf1L2O8X_6Qu2VNq9iUhCjbXTc=51702h7yo4dH=8w@mail.gmail.com> <CA+FnnTx5n=yCw4+qcRuv-c7ECf=K=tao7j_k=dDp5nPUMJ7t_A@mail.gmail.com>
In-Reply-To: <CA+FnnTx5n=yCw4+qcRuv-c7ECf=K=tao7j_k=dDp5nPUMJ7t_A@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sun, 23 Mar 2025 20:17:02 +0100
Message-ID: <CAFj8pRB5d=0WQYyK7Nho7XY2qY+rvgwzqSmWpVOYBdvC9dcDXw@mail.gmail.com>
Subject: Re: Determine server version from psql script
To: Igor Korot <ikorot01@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000435c36063107575b"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRB5d%3D0WQYyK7Nho7XY2qY%2BrvgwzqSmWpVOYBdvC9dcDXw%40mail.gmail.com>
Precedence: bulk

--000000000000435c36063107575b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

ne 23. 3. 2025 v 19:31 odes=C3=ADlatel Igor Korot <ikorot01@gmail.com> naps=
al:

> Hi,
>
> [code]
> SELECT current_setting('server_version_num')::int > 130000 as v13
> \gset
> \if :v13
>    CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSERT ON
> playersinleague WHEN new.current_rank IS NULL
>    BEGIN
>            UPDATE playersinleague SET current_rank =3D 1 + (SELECT
> coalesce( max( current_rank ), 0 ) FROM playersinleague WHERE id =3D
> new.id) WHERE rowid =3D new.rowid;
> psql:draft_pg.sql:44265: ERROR:  syntax error at or near "TRIGGER"
> LINE 1: CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSER...
>                          ^
> [/code]
>
> What am I doing wrong?
>


the test should be

SELECT current_setting('server_version_num')::int > =3D140000 as v14

\if :v14
 ...

CREATE OR REPLACE is supported from PostgreSQL 14

https://www.postgresql.org/docs/14/sql-createtrigger.html

Regards

Pavel

>
> Thank you.
>
>
> On Sun, Mar 23, 2025 at 12:53=E2=80=AFPM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> >
> > On Sunday, March 23, 2025, David G. Johnston <david.g.johnston@gmail.co=
m>
> wrote:
> >>
> >> On Sunday, March 23, 2025, Igor Korot <ikorot01@gmail.com> wrote:
> >>>
> >>>
> >>> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
> version?
> >>
> >>
> >> No.  You have to drop the trigger if it does exist and then create the
> new one.
> >
> >
> > Well, you can always query the catalogs directly to answer the question
> =E2=80=9Cdoes this trigger exist=E2=80=9D.
> >
> > David J.
> >
>
>
>

--000000000000435c36063107575b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">ne 23. 3. 2025 v=C2=A019:31 o=
des=C3=ADlatel Igor Korot &lt;<a href=3D"mailto:ikorot01@gmail.com">ikorot0=
1@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">Hi,<br>
<br>
[code]<br>
SELECT current_setting(&#39;server_version_num&#39;)::int &gt; 130000 as v1=
3<br>
\gset<br>
\if :v13<br>
=C2=A0 =C2=A0CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSERT =
ON<br>
playersinleague WHEN new.current_rank IS NULL<br>
=C2=A0 =C2=A0BEGIN<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0UPDATE playersinleague SET current=
_rank =3D 1 + (SELECT<br>
coalesce( max( current_rank ), 0 ) FROM playersinleague WHERE id =3D<br>
<a href=3D"http://new.id" rel=3D"noreferrer" target=3D"_blank">new.id</a>) =
WHERE rowid =3D new.rowid;<br>
psql:draft_pg.sql:44265: ERROR:=C2=A0 syntax error at or near &quot;TRIGGER=
&quot;<br>
LINE 1: CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSER...<br>
=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^<br>
[/code]<br>
<br>
What am I doing wrong?<br></blockquote><div><br></div><div></div><div><br><=
/div><div>the test should be=C2=A0</div><div><br></div><div>SELECT current_=
setting(&#39;server_version_num&#39;)::int &gt; =3D140000 as v14</div><div>=
<br></div><div>\if :v14</div><div>=C2=A0...</div><div><br></div><div>CREATE=
 OR REPLACE is supported from PostgreSQL 14</div><div><br></div><div><a hre=
f=3D"https://www.postgresql.org/docs/14/sql-createtrigger.html">https://www=
.postgresql.org/docs/14/sql-createtrigger.html</a></div><div><br></div><div=
>Regards</div><div><br></div><div>Pavel</div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">
<br>
Thank you.<br>
<br>
<br>
On Sun, Mar 23, 2025 at 12:53=E2=80=AFPM David G. Johnston<br>
&lt;<a href=3D"mailto:david.g.johnston@gmail.com" target=3D"_blank">david.g=
.johnston@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; On Sunday, March 23, 2025, David G. Johnston &lt;<a href=3D"mailto:dav=
id.g.johnston@gmail.com" target=3D"_blank">david.g.johnston@gmail.com</a>&g=
t; wrote:<br>
&gt;&gt;<br>
&gt;&gt; On Sunday, March 23, 2025, Igor Korot &lt;<a href=3D"mailto:ikorot=
01@gmail.com" target=3D"_blank">ikorot01@gmail.com</a>&gt; wrote:<br>
&gt;&gt;&gt;<br>
&gt;&gt;&gt;<br>
&gt;&gt;&gt; 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the ea=
rlier version?<br>
&gt;&gt;<br>
&gt;&gt;<br>
&gt;&gt; No.=C2=A0 You have to drop the trigger if it does exist and then c=
reate the new one.<br>
&gt;<br>
&gt;<br>
&gt; Well, you can always query the catalogs directly to answer the questio=
n =E2=80=9Cdoes this trigger exist=E2=80=9D.<br>
&gt;<br>
&gt; David J.<br>
&gt;<br>
<br>
<br>
</blockquote></div></div>

--000000000000435c36063107575b--