The dynamic pivot table function we wrote about in a previous post generated SQL, which then needed to be run manually. I didn’t think that was optimal.
This got me exploring options, and I finally discovered a use for \gexec as opposed to ; or \gx that I normally use. Here’s a comment I posted for the benefit of others:
Handy trick in psql – perfect for this lesson
In psql one can end a query with ; to run it, or with \gx to run it and show output in expanded mode.
Today I learned you can end a query with \gexec to:
\gexec execute query, then execute each value in its result
This can be seen by running the pivotcode() function in both ways:
SELECT pivotcode(
'rainfalls',
'location',
'year',
'SUM(raindays)',
'NUMERIC' )
;
/* Formatting alert: each line gets truncated in terminal display */
pivotcode
----------------------------------------------------------------------------
SELECT * FROM crosstab(
'
SELECT location,year,SUM(raindays) FROM rainfalls GROUP BY 1,2 ORDER BY 1,2',
SELECT DISTINCT year FROM rainfalls ORDER BY 1'
) AS newtable (
location varchar, "2012" NUMERIC, "2013" NUMERIC, "2014" NUMERIC, "2015" NUMERIC, "2016" NUMERIC, "2017" NUMERIC
);
(1 row)
versus with \gexec, which will run the output for us!
SELECT pivotcode(
'rainfalls',
'location',
'year',
'SUM(raindays)',
'NUMERIC'
) \gexec
/*
Note: the column headers have underscores prepended:
I'll fix that later
*/
location | _2012 | _2013 | _2014 | _2015 | _2016 | _2017
-----------+-------+-------+-------+-------+-------+-------
Dubai | 112 | 116 | 111 | 80 | 110 | 102
France | 114 | 111 | 113 | 85 | 120 | 110
Germany | 153 | 120 | 136 | 128 | 138 | 113
London | 116 | 138 | 137 | 101 | 117 | 110
Malaysia | 53 | 77 | 78 | 50 | 52 | 43
Qatar | 86 | 116 | 111 | 83 | 102 | 89
Singapore | 124 | 132 | 142 | 111 | 106 | 110
Sydney | 98 | 120 | 110 | 93 | 122 | 115
Tokyo | 47 | 63 | 68 | 53 | 54 | 43
(9 rows)
So, a feature I was vaguely aware of but had no clue how to use now is essential to make this lesson work in an ideal way.
