CODEWITHSUNDEEP
sqlpostgresql
coding_bird
coding_bird

Reputation: 573

SELECT subquery with 2 return values

I want to select multiple columns from a subquery. Here my minimal example:

A function that returns two values:

CREATE OR REPLACE FUNCTION dummy_function(my_text text)
RETURNS TABLE (id Integer, remark text) AS $$
BEGIN
RETURN QUERY SELECT 42, upper(my_text);
END;
$$ LANGUAGE plpgsql;

My not working query:

SELECT 
    id, 
    city_name, 
    dummy_function(city_name)
FROM 
    (SELECT 1 as id, 'Paris' as city_name
     UNION ALL
     SELECT 2 as id, 'Barcelona' as city_name
    ) AS dummy_table

My wrong result:

 id | city_name | dummy_function 
----+-----------+----------------
  1 | Paris     | (42,PARIS)
  2 | Barcelona | (42,BARCELONA)

But I would like to have a result like this:

 id | city_name | number |   new_text
----+-----------+---------------------
  1 | Paris     |  42    |   PARIS
  2 | Barcelona |  42    |   BARCELONA

Do you know how to achieve this without running the function twice?

Upvotes: 1

Views: 86

Answers (2)

klin
klin

Reputation: 121494

Use the function returning row (or set of rows) in the FROM clause:

SELECT 
    dummy_table.id, 
    city_name, 
    dummy_function.id,
    remark
FROM 
    (SELECT 1 as id, 'Paris' as city_name
     UNION ALL
     SELECT 2 as id, 'Barcelona' as city_name
    ) AS dummy_table,
    LATERAL dummy_function(city_name)

 id | city_name | id |  remark   
----+-----------+----+-----------
  1 | Paris     | 42 | PARIS
  2 | Barcelona | 42 | BARCELONA
(2 rows)

Per the documentation:

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

SELECT 
    dummy_table.id, 
    city_name, 
    df.id as number, 
    df.remark as new_text
FROM 
    (SELECT 1 as id, 'Paris' as city_name
     UNION ALL
     SELECT 2 as id, 'Barcelona' as city_name
    ) AS dummy_table,
    dummy_function(city_name) df

Upvotes: 2

Related Questions