sql - PostgreSQL 表变量

Question

T-SQL中有类似表变量的东西吗？
在 SQL Server 中它看起来像这样:

DECLARE @ProductTotals TABLE
(
  ProductID int,
  Revenue money
)

然后在程序中我可以:

INSERT INTO @ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * Quantity)
  FROM [Order Details]
  GROUP BY ProductID

并像操作普通表一样操作这个变量。

这是描述:http://odetocode.com/Articles/365.aspx

Answer 1

在 PostgreSQL 中使用临时表。对于您的示例:

CREATE TEMP TABLE product_totals (
   product_id int
 , revenue money
);

手册关于CREATE TABLE :

If specified, the table is created as a temporary table. Temporarytables are automatically dropped at the end of a session, oroptionally at the end of the current transaction (see ON COMMITbelow). The default search_path includes the temporary schema firstand so identically named existing permanent tables are not chosen fornew plans while the temporary table exists, unless they are referencedwith schema-qualified names. Any indexes created on a temporary tableare automatically temporary as well.

Unlogged tables 在 Postgres 9.1 或更高版本中是一个有点相关的功能。他们通过不写入 WAL 来节省磁盘写入.这是一个discussion of the features by Robert Haas:

此外，关于 money 数据类型:

• PostgreSQL: Which Datatype should be used for Currency?