37

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Yavanosta
  • 1,480
  • 3
  • 18
  • 27
  • If I good remember you can not declare a variable as TABLE, there is no type TABLE in postgresql. – cojack May 28 '12 at 14:07
  • 3
    The functionality is the same as a temporary table. It is just that the article claims the table variable performs best in SQL Server. – Clodoaldo Neto May 28 '12 at 14:36
  • 1
    @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. – Rob_vH Sep 12 '19 at 18:27

2 Answers2

43

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    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? – Diligent Key Presser Nov 09 '15 at 05:52
  • 4
    @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. – Erwin Brandstetter Nov 09 '15 at 13:11
  • 2
    [This thread](https://www.postgresql.org/message-id/CAFj8pRBmxtxGDKG3pAgieS6UD_aaT3PysGMBqA%3DCmDwd1Hf2hg%40mail.gmail.com) 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. – dleavitt May 28 '17 at 23:57
  • This doesn't work if connection to db is readonly. – Qwertiy Mar 20 '23 at 13:59
8

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;
   ...
Lars Blumberg
  • 19,326
  • 11
  • 90
  • 127
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • 2
    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 : DO $$DECLARE _x xx[]; BEGIN _x := ARRAY(SELECT * FROM xx); RAISE DEBUG '_x is %',_x; END$$ [2016-04-05 21:47:45] bind to [2016-04-05 21:47:45] [42601] *ERROR: subquery must return only one column* Где: PL/pgSQL function inline_code_block line 3 at assignment` – uudecode Apr 05 '16 at 18:48