sqlpostgresqlplpgsqltable-variable

PostgreSQL table variable

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

Solution

  • Use a temporary table in PostgreSQL. For your example:

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

    From the manual for 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.

    Temporary tables are only visible within the same database session and they are dropped at the end of the session, in all versions of Postgres. You can even have separate temp tables for the same db user when connected several times (several sessions). Internally, a separate temp schema is used for each session.

    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: