@David, thanks for the tip.
>Providing a concrete example might help.
My use case is a database with a large number of spatial tables. I
have written a spatial search function which, given an arbitrary table
extended with PostGIS, will search for records in that table whose
geometries are within a given distance. The return value is a SETOF
values 'geometry ID', 'distance from input geometry' and 'centroid'
with corresponding types (int, double precision, geometry).
The final desired output is a resultset consisting of all the input
tables columns as well as these two new columns showing distance and
centroid. Obviously having an ID field this can be achieved with
INNER JOIN. The ideal scenario would be to have a function which also
performs this join... something like:
BEGIN
RETURN QUERY
EXECUTE
format(
'
SELECT
%1$I.*,
dist_query.distance AS appended_distance,
dist_query.centroid AS appended_centroid
FROM %1$I
INNER JOIN distance_search(%1$L, $1, $2, %2$L) AS dist_query
ON %1$I.%2$I=dist_query.%2$I;
',
pg_typeof(table_name),
id_column_name
)
USING search_area, buffer_size;
END;
@John
>SQL tables are /not/ polymorphic.
Yes, you are quite right. I merely meant the table who's row compound
type is been passed as a polymorphic parameter.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
