Thread: proposal: condition blocks in psql
Hi
I am thinking about simplifying a deployment some multiversion PostgreSQL extensions, and scripts....
\else
\endifminor versions can be supported too
\if_ver_ge 9.2.0
\endif
> 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.
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
\endifWhat 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
--
Fabien.
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:
PavelRegardsWhat do you thinking about it?\if_ne psqlvariable\if_eq psqlvariable\if_ver_lt 9.2\if_ver_le 9.2\if_ver_ge 9.2\if_ver_gt 9.2\if_ver_eq 9.2The proposed syntax of new psql commandsWith current possibilities, we have to use DO statement, what is not optimal or possible in some use cases. The implementation of condition block (possible nested) is very simple.HiI am thinking about simplifying a deployment some multiversion PostgreSQL extensions, and scripts.
...\else\endifminor versions can be supported too\if_ver_ge 9.2.0\endif\if_def psqlvariable
>>> \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.
> 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.
2015-06-28 8:59 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:
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
\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.
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
--
Fabien.
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/pgsql2. 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\gsetecho :server_version\if :server_version = '9.4.1'\else\endifThat's bash-style string comparison Of course if we needed more granular testing of server versions, we could do this:selectm[1]::integer as v1,m[2]::integer as v2,m[3] as v3fromregexp_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\endifThe 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.
...or... driven by a query a la \gsetselectd.dt::date as month_start_date,(d.dt + interval '1 month - 1 day')::date as month_endfromgenerate_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)\gforeachbegin;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:PavelRegardsWhat do you thinking about it?\if_ne psqlvariable\if_eq psqlvariable\if_ver_lt 9.2\if_ver_le 9.2\if_ver_ge 9.2\if_ver_gt 9.2\if_ver_eq 9.2The proposed syntax of new psql commandsWith current possibilities, we have to use DO statement, what is not optimal or possible in some use cases. The implementation of condition block (possible nested) is very simple.HiI am thinking about simplifying a deployment some multiversion PostgreSQL extensions, and scripts.
...\else\endifminor versions can be supported too\if_ver_ge 9.2.0\endif\if_def psqlvariable
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.
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
--
Fabien.
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
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
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
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
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
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
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$;
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
On Sunday, June 28, 2015, Pavel Stehule <pavel.stehule@gmail.com> wrote:
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.
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_ltExample:\if_ver_le 8.3.0CREATE OR REPLACE FUNCTION unnest(anyarray)RETURNS SETOF anyelement AS $$BEGIN ... END$$ LANGUAGE plpgsql;\endifinsteadDO $xx$BEGINIF version_le(version(), '8.3.0') THENCREATE 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.
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
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