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

-----Original Message-----
From: Robert James [mailto:srobertjames@gmail.com]
Sent: Monday, September 24, 2012 9:33 AM
To: Postgres General
Subject: Running CREATE only on certain Postgres versions

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?

Find your PG version with:
SELECT version();

and continue accordingly...

Regards,
Igor Neyman

Unfortunately, SELECT VERSION() gives a long text string - parsing out
the version isn't reliable.

So, we can reduce my question to a simpler question: What's the best
way to determine if postgres is running > version x?

Or, what's the best way to determine the exact version number
programatically (ie not just a long string)

Server parameter: server_version_num

http://www.postgresql.org/docs/9.2/interactive/runtime-config-preset.html

David J.

On Sep 24, 2012, at 21:23, Robert James <srobertjames@gmail.com> wrote:

On 09/24/2012 06:40 PM, David Johnston wrote:

Server parameter: server_version_num

http://www.postgresql.org/docs/9.2/interactive/runtime-config-preset.html

To elaborate:
test=> SELECT current_setting('server_version_num');
current_setting
-----------------
90009

And yes, I know it needs to be upgraded:)

David J.

--
Adrian Klaver
adrian.klaver@gmail.com

On Tue, Sep 25, 2012 at 3:47 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

To elaborate:
test=> SELECT current_setting('server_version_num');
current_setting
-----------------
90009

Yes, but knowing that, how does he run a statement only if version
e.g. >= 80400? Is there a better way than the proposed
create/call/drop function before PG 9.0? (since 9.0 there is the DO
statement).

-- Daniele

-----Original Message-----
From: Daniele Varrazzo [mailto:daniele.varrazzo@gmail.com]
Sent: Tuesday, September 25, 2012 11:26 AM
To: Adrian Klaver
Cc: David Johnston; Robert James; Igor Neyman; Postgres General
Subject: Re: [GENERAL] Running CREATE only on certain Postgres versions

On Tue, Sep 25, 2012 at 3:47 PM, Adrian Klaver
<adrian.klaver@gmail.com> wrote:

To elaborate:
test=> SELECT current_setting('server_version_num');
current_setting
-----------------
90009

Yes, but knowing that, how does he run a statement only if version e.g.

= 80400? Is there a better way than the proposed create/call/drop

function before PG 9.0? (since 9.0 there is the DO statement).

-- Daniele

For PG versions prior to 9.0 (without DO statement) I wrote and use extensively this little function:

CREATE OR REPLACE FUNCTION exec_pgplsql_block(exec_string text)
RETURNS BOOLEAN
AS $THIS$
DECLARE lRet BOOLEAN;
BEGIN
EXECUTE 'CREATE OR REPLACE FUNCTION any_block()
RETURNS VOID
AS $BODY$ ' || exec_string || ' $BODY$LANGUAGE PLPGSQL;' ;
PERFORM any_block();
RETURN TRUE;
END;
$THIS$LANGUAGE PLPGSQL;

which accepts as a parameter ("exec_string") any "anonymous" PlPgSQL block (what DO does in later versions),
creates a function with this PlPgSQL block as a body, and executes it.

Regards,
Igor Neyman

On 09/25/2012 08:25 AM, Daniele Varrazzo wrote:

On Tue, Sep 25, 2012 at 3:47 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

To elaborate:
test=> SELECT current_setting('server_version_num');
current_setting
-----------------
90009

Yes, but knowing that, how does he run a statement only if version
e.g. >= 80400? Is there a better way than the proposed
create/call/drop function before PG 9.0? (since 9.0 there is the DO
statement).

From the OP:
"I have some code which creates a function in Postgres.."

It is unclear what that code is in its entirety. If it is straight SQL
than I see no other choice than the above create/call/drop. If it is
some other language over SQL then I could see an IF statement or its
equivalent.

-- Daniele

--
Adrian Klaver
adrian.klaver@gmail.com

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

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