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

On Mon, Jul 25, 2016 at 6:14 AM, Peter Devoy <peter(at)3xe(dot)co(dot)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.