PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

10-May-2004 Issue: 73

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

SQL 2003 Standards Approved
Notes on SQL Standards 17-Apr-2004

This is a continuation of the evaluation PostgreSQL's implementation of the SQL2003 Standards as reviewed by Andrew Eisenberg, et. al. See Issue #71 for the first part of this evaluation.

As you probably saw in Robert Treat's Weekly news, the SQL2003 was finally published. An overview of some of the changes can be seen SQL:2003 Has Been Published by Andrew Eisenberg, et al. (You may need to purchase this document or be an ACM member to access this document.)

In this article, I will informally review some of the changes reviewed by Eisenberg, et al. to see how the PostgreSQL addresses these standards. The first nine items in the list below are covered in Issue #71. Some of the examples are taken from the text by Andrew Eisenberg, et al.

Statements about whether PostgreSQL supports or does not support an element is based on 7.4 at this time. Plans for 7.5 are not addressed. Quoted remarks are from the SQL:2003 Has Been Published Document.

Overview Summarized into an Executive Summary of Changes
Change PostgreSQL
BIT and BITVARYING data types removed Supported
BIGINT data type added Supported
Permissions on running Procedures Supported
Parameter syntax in Procedures*Not Supported
Extensions added to CREATE TABLE AS Supported
Sequence Generator Added*Supported
Identity Column type*Supported
Generated Column type*Not Supported
Extensions added to CREATE TABLE LIKE Not Supported
TABLE Functions Added *Not Supported
MERGE Statement Added Not Supported
MULTISET Data Type Added Not Supported
XML data type added Not Supported
* Indicates partial or work around support

TABLE functions added

PostgreSQL supports TABLE functions as the ability to return a ROW type from a function. This is distinct from the ability to return sets or multi-sets from a function, neither of which is required by the standard. But the ideas are often related in thought and used together. See GeneralBits Issue #26 for examples of both row and set returning functions and both of these used together.

This feature, used with returning set returning functions enables parameterized views.

In PostgreSQL, to return a ROW type from a function, first a ROW type must be defined. Then the function must be defined to return that type. This example shows the return of the latest weather measurement by city from the currweather table.

	CREATE TYPE weather (
	   city text,
	   temp_in_f INTEGER,
	   humidity integer,
	   wind text,
	   forcast text );

	CREATE FUNCTION latestweather( text )
	RETURNS weather AS
	   currweather weather%ROWTYPE;
	   SELECT into currweather
	      city, temp_in_f, humidity, wind, forcast
		FROM measure_weather
		WHERE city = $1 
		ORDER BY measure_time DESC
		LIMIT 1;
		RETURN currweather;
	' LANGUAGE 'plpgsql';

The syntax in SQL2003 is somewhat different, but the functionality is the same except that SQL2003 allows for unnamed row types to be defined. This is done by using the TABLE function to define the row type in-line with the function definition. Informix IUS supported unnamed row types in this manner, however, PostgreSQL does not currently support this syntax. PostgreSQL requires the ROW type to be predefined.

In this function, the named parameter feature is used. The body of the function is SQL (and untested).

	CREATE FUNCTION latestweather( city text )
	   city text,
	   temp_in_f INTEGER,
	   humidity integer,
	   wind text,
	   forcast text )
	   SELECT city, temp_in_f, humidity, wind, forcast
		FROM measure_weather
		WHERE city = AND
		   measure_time = (
	         SELECT max(measure_time) 
	         FROM measure_weather 
	         WHERE city =; 

MERGE Statement Added

MERGE is an addition to the time worn trio of INSERT, UPDATE and DELETE. PostgreSQL does not currently support this statement.

There has been a long standing debate and discussion of this type of statement. The basic problem is when adding rows to a table, you may not know whether they are UPDATES or INSERTS. MERGE will take those rows and either INSERT or UPDATE depending on an action specified with a MATCH condition. The wordy specification covers the cases where it is ambiguous or impossible to know the exact UPDATE or INSERT to execute.

The MERGE statement would be a single statement, as distinct from the multiple statement work arounds. This means that there would be no race condition that is the underlying problem with the workarounds. These race conditions require locking the table to be sure there are no problems and this is somewhat prohibitive. One of the more common workarounds is this:


The syntax of the MERGE statement has several parts. This is an example:

	MERGE INTO inventory AS inv USING 
	(SELECT partnum, description, quantity FROM shipment) as SH
	ON (inv.partnum = sh.partnum)
	   SET quantity = inv.quantity + sh.quantity
		(partnum, description, quantity) VALUES
		(sh.partnum, sh.description, sh.quantity);
This MERGES the shipment table with the inventory table. If there exists in inventory a partnum equal to the one in the shipment, then the UPDATE statement is executed. If not, then the INSERT statement is executed.

You can visualize this as a RIGHT OUTER JOIN of the two tables. When there is no left record, then it is INSERTED. If there is a left record, the fields specified are updated.

MULTISET Data Type Added

A MULTISET Data Type is like an array type in that it is a collection of like typed elements. However, unlike an array, it is not ordered. Informix implemented this as a LIST type. PostgreSQL does not implement this as a generalized data type, however, some specific datatypes do make use of the concept.

MULTISETS can be created with a specified list or from a SELECT statement. The generalized data type specified by the SQL2003 standard enables rows to be converted into MULTISETS and MULTISETS to be converted into ROWS. To support this the AS clause enables you to specify an element alias. For example:

returns as many rows as there are in alphas and each row is comprised of a multiset containing the values from columns a, b and c in the alpha table.
	SELECT t.a, t.a*2 AS times_two
	FROM UNNEST( MULTISET[ 4, 3, 2, 1] ) AS t(a);
UNNEST takes a MULTISET argument of any type of element and returns rows with a single column. The values of the columns are the elements of the MULTISET.

Additional functionality for MULITSETS includes basic set functions union, intersection and difference. It includes the aggregate functions COLLECT, FUSION and INTERSECT.

COLLECT takes a column from a tuple and converts it to a MULTISET. FUSION performs a UNION ALL, including duplicates on a column from a selection. INTERSECT returns a MULTISET showing only those values that are common. This example is taken from the SQL2003: Published document.

JohnMULTISET['reading', 'pop-music', 'running']
SusanMULTISET['movies', 'opera', 'reading']
JamesMULTISET['movies', 'reading']
	SELECT COLLECT(friend) AS all_friends,
	   FUSION(hobbies) AS all_hobbies,
	   INTERSECTION(hobbies) AS common_hobbies
	FROM friends;
MULTISET['John','Susan','James'] MULTISET['reading','reading','reading', 'pop-music', 'running', 'opera', 'movies', 'movies']MULTISET['reading']

XML Data Type Added

This adds a new base data type of XML as specified in Database Language -- SQL, ISO/IEC 9075-*:2003. Also see Andrew Eisenberg and Jim Melton: SQL/SML is Making Good Progress. In ACM SIGMOD Record, Vol. 31, No. 2, June 2002.

Contributors: elein at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW