Thread: Return results of join with polymorphically-defined table in pl/pgsql
Hi list The example at the bottom of this (http://stackoverflow.com/questions/11740256/11751557#11751557) answer shows how the anyelement polymorphic type can be used to have a function accept -- and return rows from -- an arbitrary table decided by the user at runtime. However, I would like to create a function which returns the resultset of an INNER JOIN with table1 being polymorphic and table2 being a result set of column types which do not change. Is this possible? I have seen a solution using CREATE TEMP VIEW but I understand this could race if the function was called multiple times in the same session. Obviously there would be way to minimize that risk but things would start to get messy. Any ideas would be much appreciated. Kind regards Peter Devoy
Re: Return results of join with polymorphically-defined table in pl/pgsql
From
"David G. Johnston"
Date:
On Sunday, July 24, 2016, Peter Devoy <peter@3xe.co.uk> wrote:
However, I would like to create a function which returns the resultset
of an INNER JOIN with table1 being polymorphic and table2 being a
result set of column types which do not change. Is this possible?
Providing a concrete example might help. But, SQL requires that, at runtime, all columns and types in the final query be defined at the time of its execution. Even those coming from a function declared returning "record". You can get dynamic SQL to accomplish pretty much anything as long as you keep that rule in mind.
David J.
On 7/24/2016 4:45 PM, Peter Devoy wrote: > However, I would like to create a function which returns the resultset > of an INNER JOIN with table1 being polymorphic and table2 being a > result set of column types which do not change. Is this possible? SQL tables are /not/ polymorphic. -- john r pierce, recycling bits in santa cruz
@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.
Re: Return results of join with polymorphically-defined table in pl/pgsql
From
"David G. Johnston"
Date:
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;
CREATE FUNCTION [...]
RETURNS TABLE (primary_tbl anyelement, query_cols dist_query_type)
RETURN QUERY
EXECUTE
format($select_template$
SELECT %1$I, -- NO .*
ROW(dist_query.distance,dist_query,centroid)::dist_query_type,
FROM %1$I
JOIN distance_search(...) AS dist_query
ON (...)
[...]
Outputs two columns, one polymorphic match and one constant.
You can tack on additional columns instead using two composites but since you are forced to use a composite output column for "table1" for consistency I'd say you should use a composite output column for "table2" as well.
I couldn't figure out a way to get the output into columns.
function_tbl1 RETURNS TABLE (tbl anyelement) -- SELECT * FROM function_tbl1 explodes the single-column composite
function_tbl2 RETURNS TABLE (tbl anyelement, const text) -- SELECT * FROM function_tbl2 keeps the composite "unit-fied"
David J.
>Outputs two columns, one polymorphic match and one constant.
Nice.>I couldn't figure out a way to get the output into columns.
Thanks for having a crack!
Peter