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

Calling PostgreSQL function that returns table in Join

I have the following query:

WITH matching_data as (
 select * from consultants_matching_data_for_project(6001)
)

select 
  id,
  matching_data.city_matching

from 
  consultant_profiles
LEFT OUTER JOIN matching_data on matching_data.consultant_profile_id = consultant_profiles.id;

Is there any other way to join with matching_data result without using a WITH clause?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    It seems like suquery works fine:

    SELECT
  consultant_profiles.id,
  matching_data.city_matching
FROM
  consultant_profiles
  LEFT OUTER JOIN (
    SELECT *
    FROM consultants_matching_data_for_project(6001)
  ) AS matching_data
  ON matching_data.consultant_profile_id = consultant_profiles.id;

  2. 0

    You can move the function call directly to the from clause of the outer query:

    SELECT p.id, x.city_matching
FROM consultant_profiles p
LEFT JOIN consultants_matching_data_for_project(6001) x 
    ON x.consultant_profile_id = p.id;

    From the documentation:

    SQL Functions as Table Sources: All SQL functions can be used in the FROM clause of a query

    Login or Signup to reply.
  3. 0

    There was no need for the CTE anyway, nor a subquery. You can use the function just like a table:

    SELECT
  consultant_profiles.id,
  matching_data.city_matching
FROM
  consultant_profiles
LEFT JOIN consultants_matching_data_for_project(6001) AS matching_data
  ON matching_data.consultant_profile_id = consultant_profiles.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search