I'm trying to execute a different create or replace function signature according to version() - since anyarray & anyelement are removed in postgres 14 and replaced by anycompatiblearray & anycompatibleelement.

I can't use prepare to draft different statement strings to be executed, since they can't contain DDL.

If I just naively if/else the creates, then both are parsed and each block causes a syntax error in the postgres version it's not intended for.

Is there any way I can execute a string of DDL, or otherwise ignore the syntax error for the half that isn't going to be executed on the current version?

2 Answers 2


You can use PL/pgSQL with dynamic SQL:

   IF current_setting('server_version_num')::integer < 140000 THEN
      EXECUTE $f$CREATE FUNCTION f(anyarray) ...$f$;
      EXECUTE $f$CREATE FUNCTION f(anycompatiblearray) ... $f$;
   END IF;
  • Ah, thank you! One of my first attempts was similar, but I was missing EXECUTE $$..., trying to EXECUTE '... instead, hence ending up with that in a function. Much cleaner, and the server_version_num too, cheers.
    – OJFord
    Feb 16 at 13:57

Ah, if I wrap it inside a function or procedure, then it is possible to use execute:

DO $$
    CREATE OR REPLACE PROCEDURE pg_temp.create_foobar() LANGUAGE PLPGSQL AS $creator$
            t_arr TEXT;
            t_el TEXT;
            cmd TEXT := '
                CREATE OR REPLACE FUNCTION foobar(%1$s) RETURNS %2$s AS $fn$
                    -- ...
                $fn$ LANGUAGE ''sql'';
            IF (SELECT VERSION() LIKE 'PostgreSQL 13.%') THEN
                t_arr := 'ANYARRAY';
                t_el := 'ANYELEMENT';
            ELSE -- pg14
                t_arr := 'ANYCOMPATIBLEARRAY';
                t_el := 'ANYCOMPATIBLEELEMENT';
            END IF;
            EXECUTE FORMAT(cmd, t_arr, t_el);

    CALL pg_temp.create_foobar();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.