Question 287375 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: Join function result table

I have a table with the lat & lng for two points:

CREATE TABLE location_pairs (
  lat_1     float,
  lng_1     float,
  lat_2     float,
  lng_2     float
);

I also have a function that takes two lat/lng coordinates pairs and returns a table with several calculated columns (distance in meters, distance in degrees, etc).

Simplified function:

CREATE OR REPLACE FUNCTION distances(lng_1 float, lat_1 float, lng_2 float, lat_2 float)
    RETURNS TABLE (
      distance_m float,
      distance_l float,
      distance_actual float,
      corrected_ratio float
    ) AS $$
    BEGIN
        RETURN QUERY
            ...
        ;
    END;
$$ LANGUAGE plpgsql;

I want to write a query that will take the original table and add the columns from the function results:

 lat_1 | lng_1 | lat_2 | lng_2  | distance_m | etc
-------+-------+-------+--------+------------+-----
     0 |     0 |     1 |     1  |          x | ...

So far, what I have is this:

SELECT
  *,
  distances(lng_1, lat_1, lng_2, lat_2)
FROM location_pairs;

which gives the result

 lat_1 | lng_1 | lat_2 | lng_2  |         distances
-------+-------+-------+--------+-----------------------------
     0 |     0 |     1 |     1  | (x, y, other, results, ...)

How do I split the distances column into the columns that result from the function? I’m assuming I need to re-structure my query, but I’m not sure how.

Tags:

2

Answers


  1. 0

    Use the function like a table in the FROM:

    SELECT
      * -- use column names
FROM location_pairs
    , distances(lng_1, lat_1, lng_2, lat_2) d;
    Login or Signup to reply.
  2. 0

    You have to use the function in the FROM. But first I suggest you to create a type to avoid complaints from the server and declare the return as the type instead of as table.

    CREATE TYPE distance_t AS (
    distance_m float,
    distance_l float,
    distance_actual float,
    corrected_ratio float
 );
    
CREATE OR REPLACE FUNCTION distances(lng_1 float, lat_1 float, lng_2 float, lat_2 float)
    RETURNS distance_t AS $$
DECLARE
    result distance_t;  -- We need to declare the return variable before using it
BEGIN
    SELECT-- your calculations here separated by commas;
    INTO result; -- the data will be stored in the variable
   
    RETURN result; -- return the variable
END;
$$ LANGUAGE plpgsql;
  
SELECT * FROM distances(1.0, 2.0, 3.0, 4.0);

    If you need to join with the inputs

    SELECT *
FROM location_pairs as loc,distances(loc.lat_1,  loc.lng_1, loc.lat_2,loc.lng_2 );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search