PostgreSQL 8.4 introduced the ability to create user-defined variadic functions. These are basically functions that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. Depesz went over it two years ago in Waiting for 8.4 variadic functions, so we are a bit late to the party. In a nutshell -- variadic functions are syntactic sugar for functions that would otherwise take arrays. In this article we'll provide some more demonstrations of them to supplement Depesz article.
I was reminded that I had never explored this feature, when recently documenting one of the new PostGIS 2.0 Raster functions - ST_Reclass which employs this feature. I think ST_Reclass is a superb function and one of my favorite raster functions thus far that I hope to put to good use soon. Our new PostGIS family member,Bborie Park, is running thru our PostGIS Raster milestones much faster than I had dreamed. He's already implemented a good chunk of stuff we discussed in Chapter 13 - PostGIS Raster and had stated you probably won't see in PostGIS 2.0. He's going a bit faster than I can catalog them, so the documentation is already embarrassingly behind the fantastic functionality that is already present in PostGIS 2.0.
I'm sure all MySQL lovers remember the MySQL concat function. You've probably even got some old code lying around that still uses it, despite the fact that MySQL now supports ANSI SQL || syntax if ANSI SQL is enabled. So what to do with this stuff if rewritting is not an option. Build an SQL Concat?
CREATE FUNCTION concat(VARIADIC param_args text[]) RETURNS text AS
$$
SELECT array_to_string($1,'');
$$
LANGUAGE 'sql';
SELECT concat('My ', 'dog ', 'likes ', 'chocolate') As result;
result
---------------------
My dog likes chocolate
Someone asked this recently on PostGIS. I have always thought that PostGIS really needs an ST_Intersection aggregate function to compliment the ST_Union one. My needs for such a thing have been non-existent though. Just one of those fleeting fantasies that somewhere somebody needs an ST_Intersection aggregate function and/or a function that takes an indefinite number of geometries and intersects them. Then that someone came. So here is an example of the indefinite number of arguments. The aggregate version takes a few extra lines of code to write but is not that much more complicated.
CREATE OR REPLACE FUNCTION upgis_IntersectionMulti(VARIADIC param_geoms geometry[]) RETURNS geometry AS
$$
DECLARE result geometry := param_geoms[1];
BEGIN
-- an intersection with an empty geometry is an empty geometry
IF array_upper(param_geoms,1) > 1 AND NOT ST_IsEmpty(param_geoms[1]) THEN
FOR i IN 2 .. array_upper(param_geoms, 1) LOOP
result := ST_Intersection(result,param_geoms[i]);
IF ST_IsEmpty(result) THEN
EXIT;
END IF;
END LOOP;
END IF;
RETURN result;
END;
$$
LANGUAGE 'plpgsql';
SELECT ST_AsText(upgis_IntersectionMulti(ST_GeomFromText('LINESTRING(1 2, 3 5, 20 20, 10 11)'),
ST_Buffer(ST_Point(1,2), 10),
ST_GeomFromText('MULTIPOINT(1 2,3 6)'))) As result;
-- result --
POINT(1 2)
And of course for PostGIS 1.5+, the MultiUnion is a super trivial exercise.
-- Multi Union is even more trivial since
-- PostGIS has an ST_Union that takes an array of geometries
CREATE OR REPLACE FUNCTION upgis_UnionMulti(VARIADIC param_geoms geometry[])
RETURNS geometry AS
$$
SELECT ST_Union($1);
END;
$$
LANGUAGE 'sql';
SELECT ST_AsText(upgis_UnionMulti(ST_Buffer(ST_Point(5,5),10, 'quad_segs=1'),
ST_GeomFromText('LINESTRING(5 5, 5 10)') ,
ST_GeomFromText('POLYGON((1 2, 3 4, 2 2, 1 2))') )) As result;
result
--------
POLYGON((15 5,5.00000000000002 -5,-5 4.99999999999997,4.99999999999995 15,15 5))