Return results of join with polymorphically-defined table in pl/pgsql

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

@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

On Mon, Jul 25, 2016 at 6:14 AM, Peter Devoy <peter@3xe.co.uk> wrote:

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.

I have had a fair play and am struggling also. Seems like any work around
is going to be too unholy to be worth running.

Thanks for having a crack!

Peter