Re: Running CREATE only on certain Postgres versions

On Mon, Sep 24, 2012 at 2:32 PM, Robert James <srobertjames(at)gmail(dot)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