CODEWITHSUNDEEP
postgresqlfunctionloopsselectreturn
MyJobIsHard
MyJobIsHard

Reputation: 25

PostgreSQL return result of funtion in loop

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;
$$

Upvotes: 0

Views: 2861

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45930

Your code cannot to work from more reasons:

  1. Syntax - the nesting DO statement is absolutely useless, and more - it block any result. There is not possibility to return anything from DO statement.

  2. 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;
$$;

Upvotes: 1

Related Questions