Thread: psql metaqueries with \gexec

psql metaqueries with \gexec

From

Corey Huinker

Date:

20 February 2016, 04:32:50

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_attribute
where attrelid = 'foo'::regclass
and 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_attribute
where attrelid = 'gexec_temp'::regclass
and attnum > 0
order by attnum

\gexec

create index on gexec_temp(a)
CREATE INDEX
create index on gexec_temp(b)
CREATE INDEX
create index on gexec_temp(c)
CREATE INDEX
create 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 all
select 'select true as is_true', 'select ''2000-01-01''::date as party_over'
\gexec
ones
----
1
(1 row)

y double
- ------
1 2
2 4
3 6
4 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 block
from generate_series(1,2)

\gexec

do $$ begin raise notice 'plpgsql block executed'; end;$$
NOTICE: plpgsql block executed
DO
do $$ begin raise notice 'plpgsql block executed'; end;$$
NOTICE: plpgsql block executed
DO

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.

Thread: psql metaqueries with \gexec

Attachment

Attachment

Attachment

Attachment

Attachment