Thread: 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?
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) 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 >
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
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