From: Markus Schaber <schabi@logix-tt.com>
Subject: [postgis-users] X() function working for POINTs and MULTIPOINTs
Date: Mon, 27 Mar 2006 12:35:16 +0200
Message-ID: <4427BFE4.8050500@logix-tt.com> (raw)
In-Reply-To: <1143278826.44250cea7eccc@web-mail1.uibk.ac.at>
References: <20060324200036.ED8E52C1503@netnation.refractions.net>
<1143278826.44250cea7eccc@web-mail1.uibk.ac.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: postgis-users@lists.osgeo.org
Cc: schabi@logix-tt.com
Subject: Re: [postgis-users] X() function working for POINTs and MULTIPOINTs
In-Reply-To: <4427BFE4.8050500@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox