psql metaqueries with \gexec
| От | Corey Huinker |
|---|---|
| Тема | psql metaqueries with \gexec |
| Дата | |
| Msg-id | CADkLM=exRzVQu31kjaBPzpbu_rGUTtWDTNELNysg1ChEPSpDMQ@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: psql metaqueries with \gexec
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
| Список | pgsql-hackers |
Often, I'm faced with a long .sql script that builds some objects, then builds things on top of them.
This means that some of the queries I wish to run are dependent on the state of things that are unknown at the time of writing the script.
I could give up, and make a python script that mostly just strings together SQL statements. That's ugly and cumbersome.
I could do some wizardry like this:
$ create table foo( a integer, b text, c date);$ select coalesce( ( select string_agg(format('create index foo(%I);',attname),E'\n')from pg_attributewhere attrelid = 'foo'::regclassand attnum > 0 order by attnum),'') as sql_statements\gset:sql_statements
For those of you not willing to parse that, that's a dictionary query with a 1-column result set formatted into sql with a ';' appended, string aggregated with a newline delimiter, with the final result set coalesced with an empty string because \gset will error on an empty result set. I then immediately put that psql variable back into the command buffer, where I hope that I meta-wrote valid SQL. If it hurt to read, you can imagine what it was like to write.
I could use \g and pipe the results to another psql session...but that will happen in another transaction where my objects might not exist yet.
I would also like the log to show what commands were run.
For that reason, I created the psql command \gexec
It is like \g and \gset in the sense that it executes the query currently in the buffer. However, it treats every cell in the result set as a query which itself should be immediately executed.
$ create temporary table gexec_temp( a int, b text, c date, d float);CREATE TABLE$ select format('create index on gexec_temp(%I)',attname)from pg_attributewhere attrelid = 'gexec_temp'::regclassand attnum > 0order by attnum
\gexec
create index on gexec_temp(a)CREATE INDEXcreate index on gexec_temp(b)CREATE INDEXcreate index on gexec_temp(c)CREATE INDEXcreate index on gexec_temp(d)CREATE INDEX
Execution order of the statements is top to bottom, left to right.
$ select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)'union allselect 'select true as is_true', 'select ''2000-01-01''::date as party_over'\gexecones----1(1 row)y double- ------1 22 43 64 8(4 rows)is_true-------t(1 row)party_over----------01-01-2000(1 row)
Empty result sets do nothing:
$ select 'select 1 as expect_zero_rows ' where false\gexec
The results are just strings which are sent to SendQuery(), where they succeed or fail on their own merits
$ select 'do $$ begin raise notice ''plpgsql block executed''; end;$$' as blockfrom generate_series(1,2)
\gexec
do $$ begin raise notice 'plpgsql block executed'; end;$$NOTICE: plpgsql block executedDOdo $$ begin raise notice 'plpgsql block executed'; end;$$NOTICE: plpgsql block executedDO
I am not sure that "gexec" is the right name for this command. Others considered were \execute_each, \meta, \gmeta, \geach, as well as adding a "<" parameter to the \g command.
Many thanks to Pavel Stěhule for giving me some direction in this endeavor, though he might not agree with the design.
Вложения
В списке pgsql-hackers по дате отправления: