Re: proposal: condition blocks in psql

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(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> I am thinking about simplifying a deployment some multiversion PostgreSQL
> extensions, and scripts.
>
> With 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.
>
> The proposed syntax of new psql commands
>
> \if_ver_eq 9.2
> ...
> \else
> \endif
>
> \if_ver_gt 9.2
> \if_ver_ge 9.2
> \if_ver_le 9.2
> \if_ver_lt 9.2
>
> minor versions can be supported too
>
> \if_ver_ge 9.2.0
> \endif
>
> \if_def psqlvariable
> \if_eq psqlvariable
> \if_ne psqlvariable
>
> What do you thinking about it?
>
> Regards
>
> Pavel
>