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
psql-ans.1.diff
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers