PL/pgSQL: Why I get a 'set-returning functions' message error?

Question

I’m discovering PL/ pgSQL and the functions creation. In order to create a code more human readable I would like use custom nested functions or in other words I want call a function with the results of one other. My functions are:

  CREATE SCHEMA IF NOT EXISTS routing_roaddata_func;
  CREATE OR REPLACE FUNCTION "routing_roaddata_func".get_nav_strand_ids(link_id_v bigint ) RETURNS TABLE 
  (nav_strand_ids bigint) as $$
  BEGIN
    RETURN QUERY select nav_strand_id from rdf_wvd_211f0_1.rdf_nav_strand  where link_id = link_id_v ;
  END;
  $$ LANGUAGE plpgsql;
  
  --------------
  CREATE OR REPLACE FUNCTION "routing_roaddata_func".get_condition_type(strand_ids_v bigint) RETURNS TABLE
  (condition_id_ bigint )
  AS $$
  BEGIN
  condition_id_ := (select condition_type from rdf_wvd_211f0_1.rdf_condition  where nav_strand_id = strand_ids_v );
  END;
  $$ LANGUAGE plpgsql;
  
  ---------------
  CREATE OR REPLACE FUNCTION "routing_roaddata_func".get_condition_type2(strand_ids_v bigint, out condition_id_ bigint)
  AS $$
  BEGIN
  condition_id_ := (select condition_type from rdf_wvd_211f0_1.rdf_condition  where nav_strand_id = strand_ids_v );
  END;
  $$ LANGUAGE plpgsql;
  
  ----------------

And I get some troubles when I want use its. I try :

select "routing_roaddata_func".get_nav_strand_ids(820636761); -- works
select "routing_roaddata_func".get_condition_type("routing_roaddata_func".get_nav_strand_ids(820636761)); -- the returns is empty

select "routing_roaddata_func".get_condition_type2("routing_roaddata_func".get_nav_strand_ids(820636761)); -- works
  
select * from "routing_roaddata_func".get_condition_type("routing_roaddata_func".get_nav_strand_ids(820636761)); **--ERROR:  set-returning functions must appear at top level of FROM**

It's annoying because my final goal is to add a where clause like that to detect the presence of one modality in the returned values.

select * from "routing_roaddata_func".get_condition_type("routing_roaddata_func".get_nav_strand_ids(820636761)) where condition_type = 23;

Someone could explain me the problem of the "set-returning functions" ? How can I proceed ?

Pavel Stehule Pavel Stehule · Accepted Answer · 2021-10-14T10:21:54

Set Returning Function (in Postgres terminology - function that returns table) cannot be used as argument of any other function. You can pass more values as one parameter using by an arrays, or you can use LATERAL join:

create or replace function fx(int[])
returns table (a int, b int) as $$
begin
  for i in 1..3 loop
    foreach b in array $1 loop
      a := i; return next;
    end loop;
  end loop;
end;
$$ language plpgsql;

-- ARRAY(subselect) does an array from table
postgres=# select * from fx(array(select generate_series(1,3)));
┌───┬───┐
│ a │ b │
╞═══╪═══╡
│ 1 │ 1 │
│ 1 │ 2 │
│ 1 │ 3 │
│ 2 │ 1 │
│ 2 │ 2 │
│ 2 │ 3 │
│ 3 │ 1 │
│ 3 │ 2 │
│ 3 │ 3 │
└───┴───┘
(9 rows)

-- using LATERAL join
create or replace function fx1(int)
returns table (a int, b int) as $$
begin
  for i in 1..3 loop
     a := i; b := $1; return next;            
  end loop;  
end;
$$ language plpgsql;

-- for any row of function generate_series is called function fx1
postgres=# select fx1.*
              from generate_series(1,3) g(v),
                   LATERAL fx1(v) order by a, b;
┌───┬───┐
│ a │ b │
╞═══╪═══╡
│ 1 │ 1 │
│ 1 │ 2 │
│ 1 │ 3 │
│ 2 │ 1 │
│ 2 │ 2 │
│ 2 │ 3 │
│ 3 │ 1 │
│ 3 │ 2 │
│ 3 │ 3 │
└───┴───┘
(9 rows)

PL/pgSQL: Why I get a 'set-returning functions' message error?

1 Answers