Re: proposal: condition blocks in psql

2015-06-28 8:47 GMT+02:00 Corey Huinker <corey(dot)huinker(at)gmail(dot)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.

>
> ...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
>>
>
>