Re: Running CREATE only on certain Postgres versions

On 2012-09-24, 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?

perhaps like this?

-- UNTESTED

create function temp_foo () returns void as '
begin
if version() ~ ''PostgreSQL (7\\\\.|8\\\\.[0123]\\\\.)''
then
execute ''DROP AGGREGATE IF EXISTS array_agg(anyelement)'';
execute ''CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND=''''{}''''
);
'';
end if;
end; ' language plpgsql;
select temp_foo();
drop function temp_foo();

you may get warnings about string escapes, there's not much that can be
done about that, it should execut ok on all versions
that support create aggregate. (back to 7.0 which seems to be when
create agregate was written) UNTESTED.

--
⚂⚃ 100% natural