PLSQL function aggregate select loop

Question

I am trying to create a function in PLSQL that does the following:

Get a list of id's from my BD.
Iterate over it in a loop and do a SELECT using one of its properties in a the WHERE.
Aggregate all the loop iterations results in one single variable/whatever and return it as the result of the function. (concatenated string comma separated or anything would work)

what i got so far:

CREATE FUNCTION loop(OUT str text)
RETURNS text AS
$func$
DECLARE
    i text;
BEGIN
str := '';

FOR i IN
    SELECT id from public.factors
    LOOP
        str :=  str || ',' || #query - ex: select name from public.factors where factors.id = i # 
      -- Here i would like to do a select, return a single value from it and aggregate it to the existing value in str
    END LOOP;
END
$func$ LANGUAGE plpgsql;

PS: the logic of the example is a simplified version of what i need to do. so any simplification of its logic in a single query etc its not a valid solution to the original problem.

If you are using the same tables for your loop, then I don't think you need to go such length for it. You can just SELECT id||', '|| name from public.factors . But your ex is just random, then it would be helpful if you changed the table name in your example... — Isi
– Isi, Commented Aug 5, 2021 at 11:58

Silver · Accepted Answer · 2021-08-05 12:11:47Z

1

Here's simplest way I know:

SELECT LISTAGG (name, ',') FROM public.factors

The Oracle/PLSQL LISTAGG function concatenates values of the column for each GROUP based on the order_by_clause. But you can use it without group by and order by too.

If your cells values are more than 4000 characters, which is the max for varchar in oracle, and you are using something like CLOB, you can use this one instead, for larger strings:

SELECT XMLAGG (XMLELEMENT (NOENTITYESCAPING e, name, ',').EXTRACT ('//text()') ORDER BY name).getclobval ()
FROM public.factors

But if you want to use loop, then here's something like your example:

CREATE FUNCTION loop(OUT str text)
RETURNS text AS
$func$
DECLARE
    v_name text;
BEGIN
str := '';

FOR i IN
    SELECT id from public.factors
    LOOP
    select name into v_name from public.factors where factors.id = i.id;
        str :=  str || ',' || v_name;
    END LOOP;
END

answered Aug 5, 2021 at 12:11

Silver

335 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Isi Over a year ago

LISTTAG is really useful. But I have never heard or seen your 2nd solution, so can't say anything much about that

Collectives™ on Stack Overflow

PLSQL function aggregate select loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related