PostgreSQL table variable

Question

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

Pathless

If I good remember you can not declare a variable as TABLE, there is no type TABLE in postgresql. – Leafstalk

The functionality is the same as a temporary table. It is just that the article claims the table variable performs best in SQL Server. – Contango

@ClodoaldoNeto in SQL Server there are two options for this use case: the first is to use a temp table just as in Postgres, the second is to use a table variable. There are advantages to each depending on the circumstances. – Eolic

Answer 1

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?

Deliquescence

In PostgreSQL 9.4 can i safely assume that a new instance of temporary table is created for each user calling a function which uses temporary table even if the calls are simultaneous? – Roo

@DiligentKeyPresser: Temporary tables are only visible within the same database session and they are dropped at the end of the session. In any version 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. – Deliquescence

This thread suggests that pg arrays may be the equivalent of a T-SQL memory table, and that temporary tables may introduce a lot of additional overhead. – Rory

This doesn't work if connection to db is readonly. – Anaemic

Answer 2

You can use an array of composite type instead

CREATE TABLE xx(a int, b int);

CREATE OR REPLACE FUNCTION bubu()
RETURNS void AS $$
DECLARE _x xx[];
BEGIN
   _x := ARRAY(SELECT xx FROM xx);
   RAISE NOTICE '_x=%', _x;
   ...

Vertebral

Indeed no: CREATE TABLE xx(a int, b int); insert into xx values(1,2); insert into xx values(3,4); DO $$DECLARE _x xx[]; BEGIN _x := ARRAY(SELECT * FROM xx); RAISE DEBUG '_x is %',_x; END$$; code` [2016-04-05 21:47:45] parse <unnamed>: DO $$DECLARE _x xx[]; BEGIN _x := ARRAY(SELECT * FROM xx); RAISE DEBUG '_x is %',_x; END$$ [2016-04-05 21:47:45] bind <unnamed> to <unnamed> [2016-04-05 21:47:45] [42601] ERROR: subquery must return only one column Где: PL/pgSQL function inline_code_block line 3 at assignment` – Sayles