0

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 ?

Improve this question

1 Answer 1

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.