Re: proposal: condition blocks in psql

On Sunday, June 28, 2015, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>
>
> 2015-06-28 14:26 GMT+02:00 Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com
> <javascript:_e(%7B%7D,'cvml','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;
>
>>
>>
why require a script language in the first place, at least for this
specific use case?

CREATE FUNCTION IF (VERSION <= 8.3.0) unnest(...)

Similar to the existing CINE syntax.

I am partial to making psql more powerful and self-contained but
conditionals on versions as a primitive (albeit extension) of SQL seems
reasonable at first blush.

David J.