CODEWITHSUNDEEP
sqlpostgresqlplpgsqltable-variable
Yavanosta
Yavanosta

Reputation: 1670

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

Upvotes: 46

Views: 107458

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45760

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;
   ...

Upvotes: 9

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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:

Upvotes: 49

Related Questions