Re: proposal: condition blocks in psql

2015-06-28 14:26 GMT+02:00 Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>:

> Hi,
>
> On 06/28/2015 08:01 AM, Pavel Stehule wrote:
>
>>
>> you can use PL/pgSQL - but there are some limits
>>
>> * maintenance large plpgsql functions
>>
>> * the plpgsql functions or anonymous functions create a transaction
>> borders - what should not be wanted
>>
>
> But why is that a problem? Generally (sub)transactions are a good thing,
> but if you want, you may create one huge function. If you want to
> modularize this a bit, you may split that into multiple functions, but
> that's an option, not a requirement.
>
>
>> * I cannot to use psql variables simply in plpgsql code (plpgsql has
>> not any macro language) - so some patterns are implemented in plpgsql
>> less readable
>>
>> for example, if you can create a script that create some plpgsql
>> function for specified PostgreSQL version, then using PLpgSQL for this
>> purpose is suboptimal due less readability and maintainability
>>
>
> I don't really see how a block of PL/pgSQL code is less maintainable than
> a similar block of code written in a pseudo-scripting language specific to
> psql. Actually I'd expect exactly the opposite, as PL/pgSQL has neat
> features like exception handling and such, which is completely absent in
> psql, or proper variables, which is somehow mimicked by session variables
> in psql.
>
> If you really need to do the scripting outsite PL/pgSQL, there's plenty of
> suitable tools for that purpose IMHO. Starting with bash, or languages like
> Perl or Python.
>

<b>I don't propose psql scripting.</b>

I propose simple statement for conditional statement execution. The core of
my proposal are commands

\if_ver_gt, \if_ver_lt

Example:

\if_ver_le 8.3.0

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS $$
BEGIN ... END
$$ LANGUAGE plpgsql;

\endif

instead

DO $xx$
BEGIN
IF version_le(version(), '8.3.0') THEN

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS $$
BEGIN ... END
$$ LANGUAGE plpgsql;

END IF;
END;
$xx$;

>
> And I think the same about readability ...
>
>
> --
> Tomas Vondra http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>