Attached patch contains feature I've implemented for myself, to make
working with huge datasets easier.
I work with large datasets (1E8 - 1E9 records), and the nature of my
work is such that I must dig something out of the data on ad-hoc
basis. I spend a lot of time with psql.
Sometimes a query runs for few minutes. And when the result finally
arrives, sometimes it's too big, contains too much columns or is
ordered incorrectly. Quite often I was thinking to myself: "If only I
could run query on the result, instead of having to re-run the
original query and wait few more minutes...".
Eventually I just wrote the feature. I use it every day now and I'm
really happy with it.
*How it works*
After query result arrives, a message is displayed : "Query result
stored as :andN", where N is ordinal number.
User can then use the pseudo-variable :ansN as a relation name in
subsequent queries.
Under the hood, all the query results are stored locally, and when
:asnN variable is used, temporary table is created and populated with
the data. The variable then expands to the table's name.
Sample session:
===
anstest=# select * from quotes limit 10;
date | open | high | low | close | volume | adjclose
------------+-------+-------+-------+-------+----------+----------
2013-05-03 | 22.57 | 22.85 | 22.55 | 22.57 | 45523300 | 22.57
2013-05-02 | 22.25 | 22.32 | 22.15 | 22.32 | 27651500 | 22.32
2013-05-01 | 22.10 | 22.35 | 22.10 | 22.15 | 39201600 | 22.15
2013-04-30 | 22.29 | 22.38 | 22.21 | 22.29 | 34054800 | 22.29
2013-04-29 | 22.31 | 22.32 | 22.00 | 22.27 | 36531800 | 22.27
2013-04-26 | 21.98 | 22.40 | 21.97 | 22.21 | 47012500 | 22.21
2013-04-25 | 22.21 | 22.23 | 21.91 | 21.95 | 41462900 | 21.95
2013-04-24 | 21.69 | 22.03 | 21.65 | 21.96 | 51496600 | 21.96
2013-04-23 | 21.55 | 21.69 | 21.36 | 21.50 | 65489600 | 21.50
2013-04-22 | 21.67 | 21.68 | 21.11 | 21.35 | 87787900 | 21.35
(10 rows)
Query result stored as :ans0
anstest=# select date,close from :ans0 where date < '2013-05-01';
date | close
------------+-------
2013-04-30 | 22.29
2013-04-29 | 22.27
2013-04-26 | 22.21
2013-04-25 | 21.95
2013-04-24 | 21.96
2013-04-23 | 21.50
2013-04-22 | 21.35
(7 rows)
Query result stored as :ans1
anstest=# select * from :ans1 order by date;
date | close
------------+-------
2013-04-22 | 21.35
2013-04-23 | 21.50
2013-04-24 | 21.96
2013-04-25 | 21.95
2013-04-26 | 22.21
2013-04-29 | 22.27
2013-04-30 | 22.29
(7 rows)
Query result stored as :ans2
===
I find this feature quite useful, but I understand that my use case
may be quite unique.
If maintainers think that this is something that could be useful for
general public, I'm ready to polish any rough edges of the attached
patch, to make it suitable for inclusion.
Because the feature introduces some overhead, it should probably be
turned off by default and turned on by backslash command and/or
command-line parameter.
Maciek
