From: Markus Schaber <schabi@logix-tt.com>
Date: Mon, 27 Mar 2006 12:35:16 +0200
Subject: [postgis-users] X() function working for POINTs and MULTIPOINTs
In-Reply-To: <1143278826.44250cea7eccc@web-mail1.uibk.ac.at>
References: <20060324200036.ED8E52C1503@netnation.refractions.net>
	<1143278826.44250cea7eccc@web-mail1.uibk.ac.at>
Message-ID: <4427BFE4.8050500@logix-tt.com>

Hi, Bernhard,

Bernhard Reimar Hoefle wrote:

> I was thinking of plpgsql functions. How would such a function look like? I
> don't know how to write a plpgsql function which works like the dump()
> function. I just know to write a function like SELECT * from
> your_x('MULTIPOINT(...)')

It even does work without writing your own, using the built-in
generate_series set-returning function:

navteq=# select foo,asText(bar) from test;
 foo |         astext
-----+-------------------------
  42 | MULTIPOINT(1 2,3 4,5 6)
  23 | MULTIPOINT(7 8)
(2 rows)

navteq=# select foo,x(geometryn(bar,generate_series(1,npoints(bar))))
FROM test;
 foo | x
-----+---
  42 | 1
  42 | 3
  42 | 5
  23 | 7
(4 rows)


An appropriate set-returning function would be along the lines of:

CREATE OR REPLACE FUNCTION generate_x (geom geometry)
RETURNS SETOF double precision AS
'DECLARE
    index integer;
BEGIN

    FOR index IN 1 .. npoints(geom) LOOP
	RETURN NEXT X(geometryn(geom,index));
    END LOOP;
END
' LANGUAGE 'plpgsql' IMMUTABLE STRICT;


However it does not work for some obscure reason:

navteq=# select foo,generate_x(bar) from test;
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "generate_x" line 5 at return next

The problem is that I can call other set-valued functions in the same
context:

navteq=# select foo,dump(bar) from test;
 foo |                       dump
-----+--------------------------------------------------
  42 | ({1},0101000000000000000000F03F0000000000000040)
  42 | ({2},010100000000000000000008400000000000001040)
  42 | ({3},010100000000000000000014400000000000001840)
  23 | ({1},01010000000000000000001C400000000000002040)
(4 rows)

And the generate_series above does work, too.

I'll try to investigate this.

> But my problem is performance and the C functions are quite faster than the
> procedural scripts.

Yes, that may be the case for you.

HTH,
Schabi


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org