Re: Determine server version from psql script

Hi

ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <ikorot01(at)gmail(dot)com> napsal:

> 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 = 1 + (SELECT
> coalesce( max( current_rank ), 0 ) FROM playersinleague WHERE id =
> new.id) WHERE rowid = 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 > =140000 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 PM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > On Sunday, March 23, 2025, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
> >>
> >> On Sunday, March 23, 2025, Igor Korot <ikorot01(at)gmail(dot)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
> “does this trigger exist”.
> >
> > David J.
> >
>
>
>