proposal: condition blocks in psql

The proposed syntax of new psql commands
\if_ver_eq 9.2
...
\else
\endif

What do you thinking about it?

Couldn't this kind of thing be done directly with PL/pgSQL?

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-06-28 7:49 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

The proposed syntax of new psql commands

\if_ver_eq 9.2
...
\else
\endif

What do you thinking about it?

Couldn't this kind of thing be done directly with PL/pgSQL?

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

* 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

Pavel

I was just musing about this today, and was afraid that no one else would
want it!

This would be useful to me in the following use cases which I have right
now:

1. I have a SQL script that invokes \COPY into a temporary table or some
similar thing, preventing most of my logic from being pushed into pl/pgsql
2. The general difficulty of getting psql var values into a DO block
(currently I use temp tables).
3. (re)deployment scripts that need to work around commands that lack IF
EXISTS / IF NOT EXISTS clauses (CREATE USER MAPPING, CREATE SERVER, etc).
4. (re)deployment scripts that use commands that do have IF EXISTS / IF NOT
EXISTS and I'd like to avoid unnecessary steps.
5. I'm actually using psql to connect to redshift, which doesn't have DO
blocks at all.

I would suggest keeping the number of \if<FOO> constructs small, and
leverage existing \gset and \set abilities, with some.

If we restricted it to \if \elseif \else \endif, we'd need help from what
basically would be an eval() function. For this example I'll borrow from
bash:

show server_version
\gset
echo :server_version

\if :server_version = '9.4.1'
\else
\endif

That's bash-style string comparison Of course if we needed more granular
testing of server versions, we could do this:

select
m[1]::integer as v1,
m[2]::integer as v2,
m[3] as v3
from
regexp_matches(version(),'PostgreSQL (\d+).(\d+).(\d+) on.*') m
\gset
\if :v1 -eq 9
\if :v2 -lt 4
\echo too old to run this extension
\else
\echo installing
\endif
\endif

The above example is a good argument for having psql know how to compose
the full dot-version into a single integer, like is found in the source. Do
we expose that anywhere?

While I'm dreaming, a \foreach construct would be great, either as a
construct like \set:

\foreach x `seq 0 9`
update some_table set y = z where id % 10 = :x;
\endforeach

...or... driven by a query a la \gset

select
d.dt::date as month_start_date,
(d.dt + interval '1 month - 1 day')::date as month_end
from
generate_series('2015-01-01','2015-07-31',interval '1 month') as d(dt);
select d.dt::date as month_start_date from
generate_series('2015-01-01'::date,'2015-03-31'::date,interval '1 month')
as d(dt)
\gforeach
begin;
with to_busted as( delete from new_hotness where invoice_date between
:'month_start_date'::date and :'month_end_date'::date returning *) insert
into old_n_busted select * from to_busted;
end;
\endgforeach

Why do I want this? Couldn't a DO block do that? Yes, it could, but only as
a single transaction, and at a near total loss of visibility into what step
the DO block is currently on.

Let me know how I can be of help implementing this.

On Sat, Jun 27, 2015 at 11:59 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

\if_ver_eq 9.2

What do you thinking about it?

Couldn't this kind of thing be done directly with PL/pgSQL?

you can use PL/pgSQL - but there are some limits

* maintenance large plpgsql functions

I agree with large but that would not necessarily mean complex. Also, some
functions could be in SQL, and just the logic with PL/pgSQL.

* the plpgsql functions or anonymous functions create a transaction borders
- what should not be wanted

Hmmm... If something fails when installing an extension, a transaction
border is probably a good thing? Also, the interaction of \if with
possible BEGIN/COMMIT can lead to strange states.

* I cannot to use psql variables simply in plpgsql code (plpgsql has not
any macro language)

You can call a function with a variable as an argument:

\set foo 1234
SELECT some_function(:foo);

- so some patterns are implemented in plpgsql less readable

Which pattern?

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'm not that sure about the qualitative assessment, "DO" looks pretty neat
to me.

Moreover this is to be balanced with creating a scope/block/nesting system
in psql which is currently alien to it and would bring its own pitfalls:
psql is really intrinsically line/statement oriented, that would not be
the case with what you're proposing as this logic would be deeply changed,
and people would get stuck within a non-closed \if, this would interact
with \i in possibly strange ways, and so on (say you include a file with a
non closed \if, and then everything you type seems to be ignored, or
a BEGIN is done in an if but the COMMIT was in another which was not
activated because the conditions where not consistent...).

Basically, it looks like a potential Pandora box which is best left with
its lid on.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2. The general difficulty of getting psql var values into a DO block
(currently I use temp tables).

Maybe this means that DO should be extended in some way to allow for
parameters, at least when PL/pgSQL is used?

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-06-28 8:59 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

\if_ver_eq 9.2

What do you thinking about it?

Couldn't this kind of thing be done directly with PL/pgSQL?

you can use PL/pgSQL - but there are some limits

* maintenance large plpgsql functions

I agree with large but that would not necessarily mean complex. Also, some
functions could be in SQL, and just the logic with PL/pgSQL.

* the plpgsql functions or anonymous functions create a transaction

borders
- what should not be wanted

Hmmm... If something fails when installing an extension, a transaction
border is probably a good thing? Also, the interaction of \if with possible
BEGIN/COMMIT can lead to strange states.

* I cannot to use psql variables simply in plpgsql code (plpgsql has not

any macro language)

You can call a function with a variable as an argument:

\set foo 1234
SELECT some_function(:foo);

- so some patterns are implemented in plpgsql less readable

Which pattern?

you can use parameters for functions, but you cannot it for DO statement
(simply).

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'm not that sure about the qualitative assessment, "DO" looks pretty neat
to me.

I can do with DO almost all work, but the result is not readable - I have
to play with session variables, I have to play with more levels of custom
string separator than is necessary

Moreover this is to be balanced with creating a scope/block/nesting system

in psql which is currently alien to it and would bring its own pitfalls:
psql is really intrinsically line/statement oriented, that would not be the
case with what you're proposing as this logic would be deeply changed, and
people would get stuck within a non-closed \if, this would interact with \i
in possibly strange ways, and so on (say you include a file with a non
closed \if, and then everything you type seems to be ignored, or a BEGIN is
done in an if but the COMMIT was in another which was not activated because
the conditions where not consistent...).

Basically, it looks like a potential Pandora box which is best left with
its lid on.

I don't propose full psql scripting - my proposal is much more enhancing
the current psql macro possibilities.

the implementation of \if_version_gt is pretty simple - needs few lines of
new code

2015-06-28 8:47 GMT+02:00 Corey Huinker <corey.huinker@gmail.com>:

I was just musing about this today, and was afraid that no one else would
want it!

This would be useful to me in the following use cases which I have right
now:

1. I have a SQL script that invokes \COPY into a temporary table or some
similar thing, preventing most of my logic from being pushed into pl/pgsql
2. The general difficulty of getting psql var values into a DO block
(currently I use temp tables).
3. (re)deployment scripts that need to work around commands that lack IF
EXISTS / IF NOT EXISTS clauses (CREATE USER MAPPING, CREATE SERVER, etc).
4. (re)deployment scripts that use commands that do have IF EXISTS / IF
NOT EXISTS and I'd like to avoid unnecessary steps.
5. I'm actually using psql to connect to redshift, which doesn't have DO
blocks at all.

I would suggest keeping the number of \if<FOO> constructs small, and
leverage existing \gset and \set abilities, with some.

If we restricted it to \if \elseif \else \endif, we'd need help from what
basically would be an eval() function. For this example I'll borrow from
bash:

show server_version
\gset
echo :server_version

\if :server_version = '9.4.1'
\else
\endif

That's bash-style string comparison Of course if we needed more granular
testing of server versions, we could do this:

select
m[1]::integer as v1,
m[2]::integer as v2,
m[3] as v3
from
regexp_matches(version(),'PostgreSQL (\d+).(\d+).(\d+) on.*') m
\gset
\if :v1 -eq 9
\if :v2 -lt 4
\echo too old to run this extension
\else
\echo installing
\endif
\endif

The above example is a good argument for having psql know how to compose
the full dot-version into a single integer, like is found in the source. Do
we expose that anywhere?

While I'm dreaming, a \foreach construct would be great, either as a
construct like \set:

\foreach x `seq 0 9`
update some_table set y = z where id % 10 = :x;
\endforeach

I implemented \foreach five years ago, and this is not simple to implement
statement - so don't propose it. I wouldn't to inject full scripting
language to psql. Then it is better to use bash, perl, python.

But well designed conditional statements needs only few lines for
implementation, and be good enough for almost all tasks what I need to do
in psql. More the working with versions needs a different operation than
comparing strings or comparing numbers, and can be nice if this
functionality is available with some user friendly syntax.

Hello again Pavel,

Note that I'm not against cpp-like features on principle, I did macros for
apache configurations a very long time ago, and that I only give my 0.02€
on this, for what's the € is worth these days:-)

you can use parameters for functions, but you cannot it for DO statement
(simply).

Indeed. Maybe this is worth improving independently of adding
conditionals. Not sure about a (clean) syntax, though... Maybe psql could
export its variables somehow to PL/pgSQL. Hmmm.

the implementation of \if_version_gt is pretty simple - needs few lines of
new code.

I'm curious, how many "few" lines?

You would need a stack to manage nesting, you need some kind of more or
less condition evaluation which is open-ended in itself, you need reading
up to the end token (possibly this is more or less already available), you
need adapting the prompt to reflect the nesting, you need to deal with
badly nested scripts (say can an included file be "badly nested" on \i?),
you need to decide what to put in the input line history, depending on the
available infrastructure within psql...

I would say 100-300 few lines (but I may be proven wrong both ways), all
that for something which is already more or less doable with PL/pgSQL. I
would rather try to upgrade the PL/pgSQL experience.

--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-06-28 10:46 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

Hello again Pavel,

Note that I'm not against cpp-like features on principle, I did macros for
apache configurations a very long time ago, and that I only give my 0.02€
on this, for what's the € is worth these days:-)

you can use parameters for functions, but you cannot it for DO statement

(simply).

Indeed. Maybe this is worth improving independently of adding
conditionals. Not sure about a (clean) syntax, though... Maybe psql could
export its variables somehow to PL/pgSQL. Hmmm.

I proposed it some time - but it was rejected - and in this case, I am
thinking, so conditional block can be cleaner in psql side. I am playing
with psql long time - and I changed my opinion about full scripting support
in psql -
http://okbob.blogspot.cz/2009/12/enhanced-psql-console-second-version.html
(the more complex scripts are unreadable in psql). Instead this I would to
support integration psql with some other languages - in this moment I like
lua language. But conditional blocks in psql is little bit different topic.
I really don't want to open Pandora's box :).

the implementation of \if_version_gt is pretty simple - needs few lines of

new code.

I'm curious, how many "few" lines?

I am looking to epsql source code
http://pgfoundry.org/frs/download.php/2537/psql-enhanced-macros.diff and
with more precious estimation less than 200 rows of formatted code
(implementation \ifdef)

You would need a stack to manage nesting, you need some kind of more or
less condition evaluation which is open-ended in itself, you need reading
up to the end token (possibly this is more or less already available), you
need adapting the prompt to reflect the nesting, you need to deal with
badly nested scripts (say can an included file be "badly nested" on \i?),
you need to decide what to put in the input line history, depending on the
available infrastructure within psql...

you don't need it for \if \else \endif. In this case you can ignore some
lines from input. You have to check unexpected end and unexpected begin.

I would say 100-300 few lines (but I may be proven wrong both ways), all
that for something which is already more or less doable with PL/pgSQL. I
would rather try to upgrade the PL/pgSQL experience.

What do you think? I afraid, so there is not too space for experiments
there.

Regards

Pavel

Hi,

On 06/28/2015 09:04 AM, Fabien COELHO wrote:

2. The general difficulty of getting psql var values into a DO
block (currently I use temp tables).

Maybe this means that DO should be extended in some way to allow for
parameters, at least when PL/pgSQL is used?

I agree with this wholeheartedly. ISTM most of this thread is about
limitations of our current DO implementation, partially addressed by
adding a bunch of scripting commands, specific to psql. I don't think
that's the right solution.

regards

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-06-28 14:12 GMT+02:00 Tomas Vondra <tomas.vondra@2ndquadrant.com>:

Hi,

On 06/28/2015 09:04 AM, Fabien COELHO wrote:

2. The general difficulty of getting psql var values into a DO

block (currently I use temp tables).

Maybe this means that DO should be extended in some way to allow for
parameters, at least when PL/pgSQL is used?

I agree with this wholeheartedly. ISTM most of this thread is about
limitations of our current DO implementation, partially addressed by adding
a bunch of scripting commands, specific to psql. I don't think that's the
right solution.

This proposal is not against to DO parametrization. It is same like
conditional block in C (#ifdef). There is similarity with C statements -
and both has sense.

Pavel

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.

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

On 06/28/2015 08:47 AM, Corey Huinker wrote:

5. I'm actually using psql to connect to redshift, which doesn't have DO
blocks at all.

I don't see this as a reason to add features to psql, unless there are
other compelling reasons for the addition.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

On 06/28/2015 02:21 PM, Pavel Stehule wrote:

2015-06-28 14:12 GMT+02:00 Tomas Vondra
<tomas.vondra@2ndquadrant.com <mailto:tomas.vondra@2ndquadrant.com>>:

This proposal is not against to DO parametrization. It is same like
conditional block in C (#ifdef). There is similarity with C
statements - and both has sense.

I'm not sure how this is related to C macros - AFAIK psql has no concept
of preprocessing, so effectively all the the conditional statements are
equal. Also, they work directly with psql variables.

You mentioned several reasons for implementing this feature:

* maintenance of large plpgsql functions

* plpgsql functions create a transaction borders

* impossibility to use psql variables in plpgsql code

I don't really buy the assertion that maintenance will be easier with
large amounts of psql-specific scripting language, compared to plpgsql
or regular scripting languages (bash, python, ...). Or more readable.

I also don't see why the transaction borders should be an issue,
especially considering that the code is most often used to maintenance
tasks.

So the only thing remaining is the impossibility to use psql variables
in plpgsql code. And that's the current DO limitation, so let's fix that.

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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;
$xx$;

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
<javascript:_e(%7B%7D,'cvml','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;

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.

Hi,

On 06/28/2015 02:50 PM, Pavel Stehule wrote:

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

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

That's a matter of opinion, I guess ...

While you may propose only two simple conditional statements at the
moment, I think it'll either get unused (and then why implement it?), or
people will use that in various ways - and in that case I'd expect more
request for more and more statements, additional scripting features etc.
And that's a direction of more extensive scripting support in psql,
which I think is a bad one.

I'm already dealing with awful script mashups, and this seems like a
great way to make it even messier. I can't really imagine using any of
those commands in practice, actually.

I'd always choose a per-version script, for example. Or something that
actually builds the SQL script ...

--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I implemented \foreach five years ago, and this is not simple to
implement statement - so don't propose it. I wouldn't to inject full
scripting language to psql. Then it is better to use bash, perl, python.

But well designed conditional statements needs only few lines for
implementation, and be good enough for almost all tasks what I need to do
in psql. More the working with versions needs a different operation than
comparing strings or comparing numbers, and can be nice if this
functionality is available with some user friendly syntax.

Yes, I'll read up on that project, and set my foreach dreams aside for the
time being.

even a simple \if var_name, where var_name is judged by the accepted
PostgreSQL string values of TRUE/FALSE, would be immensely useful.

select (version() like '%9.4.1%') as is_941
\gset

\if is_941
\endif
\if is_942
\endif

2015-06-28 22:43 GMT+02:00 Corey Huinker <corey.huinker@gmail.com>:

I implemented \foreach five years ago, and this is not simple to
implement statement - so don't propose it. I wouldn't to inject full
scripting language to psql. Then it is better to use bash, perl, python.

But well designed conditional statements needs only few lines for
implementation, and be good enough for almost all tasks what I need to do
in psql. More the working with versions needs a different operation than
comparing strings or comparing numbers, and can be nice if this
functionality is available with some user friendly syntax.

Yes, I'll read up on that project, and set my foreach dreams aside for the
time being.

even a simple \if var_name, where var_name is judged by the accepted
PostgreSQL string values of TRUE/FALSE, would be immensely useful.

select (version() like '%9.4.1%') as is_941
\gset

\if is_941
\endif
\if is_942
\endif

This is design detail - there can be more variants of supported conditions.

Regards

Pavel

On Sun, Jun 28, 2015 at 1:59 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

\if_ver_eq 9.2

What do you thinking about it?

Couldn't this kind of thing be done directly with PL/pgSQL?

you can use PL/pgSQL - but there are some limits

* maintenance large plpgsql functions

I agree with large but that would not necessarily mean complex. Also, some
functions could be in SQL, and just the logic with PL/pgSQL.

* the plpgsql functions or anonymous functions create a transaction
borders
- what should not be wanted

Hmmm... If something fails when installing an extension, a transaction
border is probably a good thing? Also, the interaction of \if with possible
BEGIN/COMMIT can lead to strange states.

Manual transaction control is the killer feature IMO; not being able
to do it forces code out of sql and into a scripting language.
Transaction management in 'psql scripting' is no more or less fragile
than in most imperative languages.

Personally, I prefer a server side solution to this problem (stored
procedures) so that the application can leverage this functionality
through the protocol. However, psql extensions are probably worth it
in their own right.

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers