Variadic Functions in PostgreSQL   

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 array_to_string($1,'');
$$
LANGUAGE 'sql';

SELECT concat('My ', 'dog ', 'likes ', 'chocolate') As result;

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[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))