accessing sql aggregate method results with RAISE NOTICE in Postgres function loop

Question

In a Postgres function, I'm trying to loop through a select statement that uses various aggregate functions, such as count() and length(). Like so:

    DECLARE stats RECORD;
    FOR stats IN SELECT length(something), count(something_else) from widgets LOOP
    END LOOP;

Within that loop, I want to call RAISE NOTICE on the results.

Ordinarily (if I weren't using aggregate functions) I would do the following:

    FOR stats IN SELECT something, something_else from widgets LOOP
       RAISE NOTICE '% %', something, something_else;
    END LOOP;

So, basically, it's just the aggregate part that is throwing me off. How can I call RAISE NOTICE on the results of the aggregate functions?

I know I can just pass the entire stats record into RAISE NOTICE and get a bracketed result, but I figure there must be a way to isolate each aggregated result.

I tried COUNT() AS and LENGTH() AS, but I'm still not sure I can pass those "as" variables to the RAISE NOTICE.

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2013-04-01T14:57:31

Give the results an identifier:

FOR stats IN 
    SELECT length(something) something, count(something_else) something_else 
    from widgets
    group by 1
loop
    RAISE NOTICE '% %', stats.something, stats.something_else;
END LOOP;

But notice that you will need to use a group by to count as length is not an aggregate function;

accessing sql aggregate method results with RAISE NOTICE in Postgres function loop

1 Answers