Re: proposal: condition blocks in psql - Mailing list pgsql-hackers

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:

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