2

I created a query in PSQL and run and would like to save it. Query example:

CREATE VIEW total_revenue_per_customer AS SELECT
customers.id, customers.first_name, customers.last_name,
SUM(items.price) FROM customers                                                          
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id;

What is the commando to save my query by terminal?

Bruno
  • 133
  • 1
  • 11
  • See this [post](https://stackoverflow.com/questions/14634322/how-to-see-the-create-view-code-for-a-view-in-postgresql) for retreiving the `create` statement of an existing view – JGH Mar 10 '19 at 14:21

2 Answers2

2

To start, I create an empty text file in my working directory:

filename.txt     ("remember to give permission to edit and modify")

Now, using the psql meta-command,\o we can arrange for future query results to be written to the provided filename.

\o filename.txt

Displayed above, we can see one variation of the easy syntax structure is:

\o filename.txt

creating my query and run

CREATE VIEW total_revenue_per_customer AS SELECT
customers.id, customers.first_name, customers.last_name,
SUM(items.price) FROM customers                                                          
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id;

Saved in out.txt

Since the \o meta-command is still active in the query buffer, you can clear it out, by issuing another \o meta-command, and continue on with normal activity, without writing anything else to the specified file.

Bruno
  • 133
  • 1
  • 11
0

Last executed command can be also opened in external editor from psql. The command is \e which on most systems will open the command buffer in vi. This will create a temporary file but nothing stops you from saving another copy outside of the /tmp directory. Also, you can control your editor of choice with the environment variable EDITOR, which can be set without closing current psql session using \setenv command.

See the docs for psql if you want to find more useful commands. It reads really well and is in fact very helpful.

jakub-olczyk
  • 113
  • 6