Re: proposal: condition blocks in psql - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: proposal: condition blocks in psql |
| Date | |
| Msg-id | CADkLM=e1opmTpbna2GHeixDNh27YEFJ=9Zg4EePPEWbv2GW6Bg@mail.gmail.com Whole thread Raw |
| In response to | proposal: condition blocks in psql (Pavel Stehule <pavel.stehule@gmail.com>) |
| Responses |
Re: proposal: condition blocks in psql
(Fabien COELHO <coelho@cri.ensmp.fr>)
Re: proposal: condition blocks in psql (Pavel Stehule <pavel.stehule@gmail.com>) Re: proposal: condition blocks in psql (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
| 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:
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
pgsql-hackers by date: