Search code examples
sqlpostgresqlset-returning-functions

How do I SELECT multiple values from a function into separate columns?

Here is the query I have

SELECT
    *
FROM (
    SELECT 
        getlatlng("geom")
    FROM
        mytable
) AS subquery;

which returns

(-121.9,36.4)
(-117.1,32.9)
(-121.9,36.5)
(-71.2,42.6)

I'd like to write

SELECT
    subquery.lat, subquery.lon 
FROM (
    SELECT 
        getlatlng("mygeom")
    FROM
        mytable
) AS subquery;

and have it return

lat  | lon
-------------
36.4 | -121.9
32.9 | -117.1
36.5 | -121.9
42.6 | -71.2

Here are the relevant bits of the function.

CREATE OR REPLACE FUNCTION getlatlng(geom geometry, OUT longitude text, OUT latitude text)
 RETURNS record
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT ...;
$function$
Solution

  • Try:

    SELECT (p).longitude, (p).latitude FROM (
    SELECT getlatlng("mygeom") p 
      FROM mytable
) subquery

    The OUT parameters of the function define the columns of the record returned.

    Example fiddle.