Thread: Running CREATE only on certain Postgres versions

Running CREATE only on certain Postgres versions

From
Robert James
Date:
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?


Re: Running CREATE only on certain Postgres versions

From
Daniele Varrazzo
Date:
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


Re: Running CREATE only on certain Postgres versions

From
Igor Neyman
Date:
> -----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


Re: Running CREATE only on certain Postgres versions

From
Robert James
Date:
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)

On 9/24/12, Igor Neyman <ineyman@perceptron.com> wrote:
>> -----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
>


Re: Running CREATE only on certain Postgres versions

From
David Johnston
Date:
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:

> 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)
>
> On 9/24/12, Igor Neyman <ineyman@perceptron.com> wrote:
>>> -----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
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



Re: Running CREATE only on certain Postgres versions

From
Adrian Klaver
Date:
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


Re: Running CREATE only on certain Postgres versions

From
Daniele Varrazzo
Date:
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


Re: Running CREATE only on certain Postgres versions

From
Igor Neyman
Date:
> -----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


Re: Running CREATE only on certain Postgres versions

From
Adrian Klaver
Date:
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


Re: Running CREATE only on certain Postgres versions

From
Jasen Betts
Date:
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