Returning both geometry and other attributes columns in PL/pgsql function

Question

I am trying to create a function in PL/pgSQL (Postgres 9.5) (by dynamically executing a query) to return some desired columns from two tables (ad and street) in PostgreSQL/postgis database. Table 'ad' contains 87 rows while table 'street' contains 16,060 rows. What I have tried so far is given by the following code. The execution time for this code is 2.9 seconds. I am aware that there can be many different ways to improve the execution time. Here is the code:

Create or Replace Function str_con(ad geometry, street geometry)
Returns Table(address_id integer, address_locations geometry, nearest_streets geometry, traf_speed numeric, street_type character varying) AS $$ Begin
    Return Query 
    Execute 'Select
            address_id,
            address_locations,
            nearest_streets,
            traf_speed,
            street_type

        From
            (
            Select
                ad.gid As address_id,
                ad.geom As address_locations,
                st.geom As nearest_streets,
                st.trafsp As traf_speed,
                st.ospmstty As street_type
            From        
                public.ad, public.st

                Where ST_DWithin(ad.geom, st.geom, 50.0)
                Order By address_id, St_Distance(st.geom, ad.geom)

            ) As foo'; End; $$ Language 'plpgsql';

I am calling the function by this command.

Select str_con(ad.geom, st.geom) from ad
Join st On st.gid = ad.gid;

The two tables 'ad' and 'street' has geometry columns as well as other information of addresses and streets of the region of interest. I want to get both geometry and other columns as an output. But, I am getting the output of this function call like this:

Means the function is returning the set of records instead of five desired columns which is not required. Can someone please advice me that how to return the both geometry and attributes' columns from tables ad and street?

Mike T Mike T · Accepted Answer · 2016-05-16T21:36:52

To get a field from a record, use a . followed by either the field name, e.g.

SELECT (str_con(ad.geom, st.geom)).street_type FROM ...

or get all of the fields from the record with .*, e.g.

SELECT (str_con(ad.geom, st.geom)).* FROM ...

Returning both geometry and other attributes columns in PL/pgsql function

1 Answers