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
	'
	DECLARE
	   currweather weather%ROWTYPE;
	BEGIN
	   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;
	END;
	' 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 )
	RETURNS TABLE (
	   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 = latestweather.city AND
		   measure_time = (
	         SELECT max(measure_time) 
	         FROM measure_weather 
	         WHERE city = latestweather.city); 
	' LANGUAGE 'SQL';

MERGE Statement Added

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:

	INSERT INTO FOO SELECT 'XX', 'Unknown' 
	   WHERE NOT EXISTS (SELECT TRUE FROM FOO WHERE pkfield='XX');

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

	MERGE INTO inventory AS inv USING 
	USING
	(SELECT partnum, description, quantity FROM shipment) as SH
	ON (inv.partnum = sh.partnum)
	WHEN MATCHED THEN UPDATE
	   SET quantity = inv.quantity + sh.quantity
	WHEN NOT MATCHED THEN INSERT
		(partnum, description, quantity) VALUES
		(sh.partnum, sh.description, sh.quantity);

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

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:

	SELECT MULTISET( SELECT a, b, c FROM alphas); 

	SELECT t.a, t.a*2 AS times_two
	FROM UNNEST( MULTISET[ 4, 3, 2, 1] ) AS t(a);

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.

	SELECT COLLECT(friend) AS all_friends,
	   FUSION(hobbies) AS all_hobbies,
	   INTERSECTION(hobbies) AS common_hobbies
	FROM friends;

XML Data Type Added

Contributors: elein at varlena.com