PostgreSQL table variable
plpgsql, postgresql, sql, table-variable
Solution
Use a temporary table in PostgreSQL. For your example:
CREATE TEMP TABLE product_totals (
product_id int
, revenue money
);
The manual about `CREATE TABLE`:
If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see `ON COMMIT` below). The default `search_path` includes the temporary schema first and so identically named existing permanent tables are not chosen for new plans while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.
Unlogged tables in Postgres 9.1 or later are a somewhat related feature. They save disk writes by not writing to WAL. Here is a discussion of the features by Robert Haas:
Aside, concerning the `money` data type:
- PostgreSQL: Which Datatype should be used for Currency?
Problem
Is there anything like table variables in T-SQL? In Sql Server it looks like this: ``` DECLARE @ProductTotals TABLE ( ProductID int, Revenue money ) ``` Then in procedure I can: ``` INSERT INTO @ProductTotals (ProductID, Revenue) SELECT ProductID, SUM(UnitPrice * Quantity) FROM [Order Details] GROUP BY ProductID ``` And manipulate with this variable like an ordinary table. Here is description: http://odetocode.com/Articles/365.aspx