in my loop I would like to return the result of an SQL query here:
select mybd.service_offer_01 (rec.id_v2, $ 2, $ 3); Do you have an idea ?
Thank you
CREATE OR REPLACE FUNCTION select_price(param1 TEXT, param2 TEXT,param3 TEXT)
RETURNS TABLE (
id_v2 TEXT
prix prix_type
)
AS $$
do
$$
declare
rec record;
begin
for rec in select regexp_split_to_table($1,',') as id_v2
loop
select mybd.service_offre_01(rec.id_v2,$2,$3);
end loop;
end;
$$
Your code cannot to work from more reasons:
Syntax - the nesting DO statement is absolutely useless, and more - it block any result. There is not possibility to return anything from DO statement.
The result of plpgsql is realized by RETURN statement. For table function, like your, you should to use RETURN NEXT or RETURN QUERY. RETURN NEXT sends one row to output, RETURN QUERY sends a result of some query. It is well documented. Please, read doc - https://www.postgresql.org/docs/current/plpgsql.html. The lot of pattern are described here - and documentation reading is only work for few hours. This is good investment :)
So your code can looks like (I have not detailed info about function service_offre_01):
CREATE OR REPLACE FUNCTION select_price(param1 TEXT, param2 TEXT,param3 TEXT)
RETURNS TABLE (id_v2 TEXT, prix prix_type)
AS $$
DECLARE rec record;
BEGIN
FOR rec IN SELECT regexp_split_to_table($1,',') AS id_v2
LOOP
RETURN QUERY SELECT * FROM mybd.service_offre_01(rec.id_v2,$2,$3);
END LOOP;
END;
$$;
