Re: proposal: condition blocks in psql

On Sunday, June 28, 2015, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-06-28 14:26 GMT+02:00 Tomas Vondra <tomas.vondra@2ndquadrant.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;

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.