Re: Running CREATE only on certain Postgres versions

On Mon, Sep 24, 2012 at 2:32 PM, Robert James <srobertjames@gmail.com> wrote:
> I have some code which creates a function in Postgres, taken from
> http://wiki.postgresql.org/wiki/Array_agg .
>
> DROP AGGREGATE IF EXISTS array_agg(anyelement);
> CREATE AGGREGATE array_agg(anyelement) (
> SFUNC=array_append,
> STYPE=anyarray,
> INITCOND='{}'
> );
>
> The function was added in 8.4, and so the code fails when run on 8.4 or higher.
>
> How can I make the code cross-version compatible? For instance, how
> can I tell it to check the version, and only run if 8.3 or lower?   Or
> another way to make it cross-version?

You could create a plpgsql function that tries to creates the object
catching the exception, then call the function and drop it. Something
like the following (untested):

    create function try_to_create_aggregate() language plpgsql as $$
    begin
        begin
            execute $agg$
    DROP AGGREGATE IF EXISTS array_agg(anyelement);
    CREATE AGGREGATE array_agg(anyelement) (
    ...
        $agg$
        exception
            see here to know how to handle
            http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
        end;
    end
    $$;

    select try_to_create_aggregate();

    drop function try_to_create_aggregate();

In more recent postgres versions you can use "do" avoiding to create
the function.

-- Daniele