Обсуждение: 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

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?

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.