CODEWITHSUNDEEP
sqlpostgresql
CezarySzulc
CezarySzulc

Reputation: 2007

Pass select statment as an argument to sql function

I have function:

create or replace function provide_city_name(id_offer int)
returns varchar                                       
language plpgsql
as
$$
declare
   city_record record;                               
begin
   select id, type, parent_id, name 
   into city_record                             
   from location
   where id = id_offer;
   
   if city_record.type < 6 then                
    return city_record.name;                    
   else
    return provide_city_name(city_record.parent_id); 
   end if;
end;
$$;

What I want to do is to pass a SELECT result as an argument of a function. My select statment: select offer_id from offers limit 5 My result is:

     offer_id
1    146
2    147
3    148
4    149
5    150

I would like to convert this by my function:

select provide_city_name(select offer_id from offers limit 5)

And result should looks like

     offer_id
1    New York
2    Las Vegas
3    London
4    New York
5    Moscow

How can I run my function with argument select statment?

Upvotes: 0

Views: 74

Answers (1)

JoshuaG
JoshuaG

Reputation: 400

In Postgres 9.3 or later, you could try to use a LATERAL join:

SELECT o.id, o.name 
FROM   offers O
LEFT   JOIN LATERAL provide_city_name(o.offer_id) f ON true

Upvotes: 1

Related Questions