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
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 AddedMERGE 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:
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);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 AddedA 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:
SELECT MULTISET( SELECT a, b, c FROM alphas);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.
SELECT COLLECT(friend) AS all_friends, FUSION(hobbies) AS all_hobbies, INTERSECTION(hobbies) AS common_hobbies FROM friends;
XML Data Type AddedThis 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.
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