Conditionally execute DDL, short-circuiting syntax error

Question

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?

Answer 1

You can use PL/pgSQL with dynamic SQL:

DO
$$BEGIN
   IF current_setting('server_version_num')::integer < 140000 THEN
      EXECUTE $f$CREATE FUNCTION f(anyarray) ...$f$;
   ELSE
      EXECUTE $f$CREATE FUNCTION f(anycompatiblearray) ... $f$;
   END IF;
END;$$;

Answer 2

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

DO $$
BEGIN
    CREATE OR REPLACE PROCEDURE pg_temp.create_foobar() LANGUAGE PLPGSQL AS $creator$
        DECLARE
            t_arr TEXT;
            t_el TEXT;
            cmd TEXT := '
                CREATE OR REPLACE FUNCTION foobar(%1$s) RETURNS %2$s AS $fn$
                    -- ...
                $fn$ LANGUAGE ''sql'';
            ';
        BEGIN
            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);
        END;
    $creator$;

    CALL pg_temp.create_foobar();
END;
$$
;