Chocolate and Peanut Butter Cross-Breeding with PostgreSQL, SQL Server 2008, ...

• SQL Server 2008 supports default arguments for functions, but no function name overloading and requires missing ones have to be written out as DEFAULT. Aliasing function tables in FROM is also optional, but you can't alias function field names. So lets say you want to use generate_series without specifying a step argument. Then you would have to do this.

  	
  	SELECT * 
  		FROM 
  			generate_series(1,10,DEFAULT);
  			
  	--or --
  	SELECT * 
  		FROM 
  			generate_series(1,10,1);
  			
  	--or --
  		SELECT a.* 
  		FROM 
  			generate_series(1,10,1) As a;
  			
  	--or 
  	--We use intValue here because 
  	--that happens to be the column name Simon chose
  	SELECT a.IntValue
  		FROM 
  		generate_series(1,10,1) As a;
  			
  	
  	
  

• PostgreSQL which does have a built-in generate_series function, does not support default arguments for functions, but DOES support function name overloading and argument loading. So although PostgreSQL has generate_series implemented as 4 separate functions, to the user's eye it appears as one function that can take different numbers of arguments. It also requires you alias your table if it is in the FROM clause, but aliasing the field names is optional. Note also the generate_series doesn't return a table, but a set of numbers which means there is no column name really. Which means you can do:

  	
  	SELECT a.* 
  		FROM 
  			generate_series(1,10) As a;
  			
  	--or --
  	SELECT a 
  		FROM 
  			generate_series(1,10,1) As a;
  	---or --
  	SELECT a.IntValue
  		FROM 
  			generate_series(1,10) As a(IntValue);
  	
  

• Now Oracle - here I'm speaking from just looking at how Simon is using the Oracle functions and how he defined the generate_series for Oracle. Oracle supports DEFAULT arguments much like SQL Server 2008, but you don't need to output them. So to the user experience it behaves like the way PostgreSQL implementation behaves except for that annoying TABLE(..) wrap thing. So the interface looks like PostgreSQL but the underlying implementation is very different. I'm actually not clear if Oracle supports function name overloading or not.

  This by the way is the way VB.NET works too which is one of the reasons I prefer it over the current incarnation of C#. Luckily I guess enough people whined about these things so Anders Hejlsberg (of C#, Turbo Pascal and Delphi fame) finally had to yield and make C# less annoying and adopt some VB.NET luxuries which he calls Co-Evolution. I call it A band of chocolate lovers and another band of peanut butter lovers crashing into each other and realizing sometimes the other sides food tastes yummier.. Of course we won't be seeing this until C# 4. Checkout the Anders videos and Miguel's notes.

  Anyrate back to the Oracle story -- you would use it like this.

  	
  	SELECT * 
  		FROM 
  			TABLE(generate_series(1,10)) As a;
  			
  	--or --
  	SELECT * 
  		FROM 
  			TABLE(generate_series(1,10,1)) As a;
  	
  	
  

  That TABLE thing I think looks cooky just like the dumb DUAL thing. Do Oracle people still need to do DUAL when outputting from nowhere and why did they call it DUAL and not NOWHERE?

The other thing that is interesting about Oracle is that it supports a concept called PIPELINING. PIPELINING based on my understanding is the idea that a function can return data before it is done processing and the calling query can say - I've seen enough or something like that. I think DBII supports this too. This is useful for table functions that return large numbers of records.

PostgreSQL to my knowledge doesn't support that, however it supports SQL functions which are transparent to the planner and get in-lined into the plan, so for certain kinds of set returning functions, you actually probably get better speed and behavior than you would get with true pipelining. Well that's just a guess.

SQL Server 2005/2008 I have yet to test that out. For OPENQUERY linked table stuff it does seem to be supporting some sort of pipelining, but I suspect for functions, they are just opaque and output all at once.

Well after all this whining, I'm happy I can now do something like this in SQL Server 2008 to extrude my MULTIPOLYGON geometries into POLYGONs.

SELECT geom.STGeometryN(n.IntValue)
FROM towns_singular 
	CROSS APPLY 
generate_series(1,geom.STNumGeometries(), DEFAULT) As n

In doing the above, I noticed one questionable but very convenient behavior. In PostGIS -- if you pass a POLYGON to ST_NumGeometries() you get NULL and you can only use ST_GeometryN with MULTI or geometry collections. But SQL Server doesn't seem to care about those minutia. It performs the naturally assumed behavior - returning 1 for a POLYGON and the polygon again for itself.

The same trick in PostGIS if you had a mixed bag of POLYGONS and MULTIPOLYGONS which if you follow PostGIS best-practice would never happen anyway would be

	
	SELECT ST_GeometryN(ST_Multi(the_geom),generate_series(1, 
				ST_NumGeometries(ST_Multi(the_geom))))
		FROM towns_singular;
		
	--If you follow best practice then you can do this 
	--because you know your table are all MULTI
		SELECT ST_GeometryN(the_geom, generate_series(1,  
				ST_NumGeometries(the_geom)))
		FROM towns_singular;
		
	--Though if you don't care about using 
	--standard compliant functions and you want to completely extrude
	--the below is much more efficient 
	--and will work for both singular 
	--and MULTIs and will 
	--recursively break down Geometry Collections
	
	SELECT (ST_Dump(the_geom)).geom
	FROM towns_singular;
	

Check out Simon's Oracle ExplodeGeometry function for similar Oracle tricks.

Now if you look at IBM docs http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.spatial.doc/spat165.htm They only mention this function working with MULTI and Collections so it seems it behaves the same as PostGIS. I think the OGC specs say it should work for MULTI, Collections, but I'm not sure it absolutely says it shouldn't work for singular geometries. If it does then I suppose SQL Server is conveniently in violation of a stupid side of the specs.