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>
In-Reply-To: <473602.1742698901@sss.pgh.pa.us>
From: Igor Korot <ikorot01@gmail.com>
Date: Sun, 23 Mar 2025 12:38:18 -0500
Message-ID: <CA+FnnTztyXpMxEiKTZhxMtcQ8Nm6D+M1jbfJFfkJYU9dyvTeLg@mail.gmail.com>
Subject: Re: Determine server version from psql script
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Adrian Klaver <adrian.klaver@aklaver.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b0610b063105f412"
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTztyXpMxEiKTZhxMtcQ8Nm6D%2BM1jbfJFfkJYU9dyvTeLg%40mail.gmail.com>
Precedence: bulk

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

Hi, Tom,


On Sat, Mar 22, 2025, 10:01=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Igor Korot <ikorot01@gmail.com> writes:
> > On Sat, Mar 22, 2025, 8:58=E2=80=AFPM David G. Johnston <
> david.g.johnston@gmail.com>
> > wrote:
> >> Then read the psql docs.  Your version has \if and you=E2=80=99ll find=
 server
> >> version listed as the available client variables.
>
> > I was hoping for something like
>
> > If server_version >=3D X:
> >      CREATE OR REPLACE TRIGGER...
>
> psql's \if doesn't (yet) have any native expression evaluation
> ability, so you have to farm out the ">=3D" comparison.  The
> psql docs suggest relying on the server to do it, which would
> go along the lines of
>
> select current_setting('server_version_num')::int >=3D 130000 as v13
> \gset
> \if :v13
>    ... do something
> \else
>    ... do something else
> \endif
>

Thank for the code.

2 things, however.
1. Apparently CREATE OR RELACE TRIGGER syntax is available since v17, which
is the current one. So I hadto adjust the numbers.. =F0=9F=98=80
2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier version=
?

Thank you.

>
> You could also do the comparison client-side, along the lines of
>
> \set v13 `expr :SERVER_VERSION_NUM \>=3D 130000`
> \if :v13
>   ... etc
>
> But that introduces assorted platform dependencies and requires
> close attention to correct shell quoting, so it's seldom
> preferable IMO.
>
>                         regards, tom lane
>

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

<div dir=3D"auto"><div>Hi, Tom,<div dir=3D"auto"><br></div><br><br><div cla=
ss=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_at=
tr">On Sat, Mar 22, 2025, 10:01=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:t=
gl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pa=
dding-left:1ex">Igor Korot &lt;<a href=3D"mailto:ikorot01@gmail.com" target=
=3D"_blank" rel=3D"noreferrer">ikorot01@gmail.com</a>&gt; writes:<br>
&gt; On Sat, Mar 22, 2025, 8:58=E2=80=AFPM David G. Johnston &lt;<a href=3D=
"mailto:david.g.johnston@gmail.com" target=3D"_blank" rel=3D"noreferrer">da=
vid.g.johnston@gmail.com</a>&gt;<br>
&gt; wrote:<br>
&gt;&gt; Then read the psql docs.=C2=A0 Your version has \if and you=E2=80=
=99ll find server<br>
&gt;&gt; version listed as the available client variables.<br>
<br>
&gt; I was hoping for something like<br>
<br>
&gt; If server_version &gt;=3D X:<br>
&gt;=C2=A0 =C2=A0 =C2=A0 CREATE OR REPLACE TRIGGER...<br>
<br>
psql&#39;s \if doesn&#39;t (yet) have any native expression evaluation<br>
ability, so you have to farm out the &quot;&gt;=3D&quot; comparison.=C2=A0 =
The<br>
psql docs suggest relying on the server to do it, which would<br>
go along the lines of<br>
<br>
select current_setting(&#39;server_version_num&#39;)::int &gt;=3D 130000 as=
 v13<br>
\gset<br>
\if :v13<br>
=C2=A0 =C2=A0... do something<br>
\else<br>
=C2=A0 =C2=A0... do something else<br>
\endif<br></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"=
auto">Thank for the code.</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">2 things, however.</div><div dir=3D"auto">1. Apparently CREATE OR RELACE =
TRIGGER syntax is available since v17, which is the current one. So I hadto=
 adjust the numbers.. =F0=9F=98=80</div><div dir=3D"auto">2. Is there a way=
 to do CREATE TRIGGER IF NOT EXIST for the earlier version?</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">Thank you.</div><div dir=3D"auto"><di=
v class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">
<br>
You could also do the comparison client-side, along the lines of<br>
<br>
\set v13 `expr :SERVER_VERSION_NUM \&gt;=3D 130000`<br>
\if :v13<br>
=C2=A0 ... etc<br>
<br>
But that introduces assorted platform dependencies and requires<br>
close attention to correct shell quoting, so it&#39;s seldom<br>
preferable IMO.<br>
<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 regards, tom lane<br>
</blockquote></div></div></div>

--000000000000b0610b063105f412--