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.
MySQL Concat() - Oh no!
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 concat('My ', 'dog ', 'likes ', 'chocolate') As result;
My dog likes chocolate
PostGIS intersection taking an unspecified number of geometries
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;
IF array_upper(param_geoms,1) > 1 AND NOT ST_IsEmpty(param_geoms) THEN
FOR i IN 2 .. array_upper(param_geoms, 1) LOOP
result := ST_Intersection(result,param_geoms[i]);
IF ST_IsEmpty(result) THEN
SELECT ST_AsText(upgis_IntersectionMulti(ST_GeomFromText('LINESTRING(1 2, 3 5, 20 20, 10 11)'),
ST_GeomFromText('MULTIPOINT(1 2,3 6)'))) As result;
And of course for PostGIS 1.5+, the MultiUnion is a super trivial exercise.
CREATE OR REPLACE FUNCTION upgis_UnionMulti(VARIADIC param_geoms geometry)
RETURNS geometry AS
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;
POLYGON((15 5,5.00000000000002 -5,-5 4.99999999999997,4.99999999999995 15,15 5))
Tracked: Jan 25, 15:57