Dynamic fieldnames in subquery?

Question

I have a table similar to the following:

CREATE TABLE stats (
  name character varying(15),
  q001001 numeric(9,0),
  q001002 numeric(9,0),
  q001003 numeric(9,0),
  q001004 numeric(9,0),
  q001005 numeric(9,0)
)

I need to query this table for the sums of various fields within, like this:

SELECT sum(q001001) as total001,
       sum(q001002) as total002,
       sum(q001005) as total005,
FROM stats;

This produces a result with ONE row of data, and THREE columns.
However, I need the results to be listed the other way around, for reporting purposes. I need THREE rows and ONE column (well, two actually, the first being the field that was sum'd) like this:

FieldName | SUM
----------+-------
q001001   |  12345
q001002   |  5432
q001005   |  986

I'd like to use some SQL like this, where the field_name (from a lookup table of the field names in the stats table) is used in a sub-query:

select l.field_name, (select sum(l.field_name) from stats)
from stats_field_names_lookup as l
where l.field_name in ('Q001001', 'Q001002', 'Q001005');

The thinking here is that sum(l.field_name) would be replaced by the actual field name in question, for each of those in the WHERE clause, and then evaluated to provide the correct sum'd result value. This, however, fails with the following error:

function sum(character varying) does not exist

because the value there is a text/character. How can I cast that character value to an unquoted string to be evaluated properly?

This SQL works. But, of course, gives the same sum'd values for each field_name, since it is hard coded as q001001 here.

select l.field_name, (select sum(q001001) from stats)
from stats_field_names_lookup as l
where l.field_name in ('Q001001', 'Q001002', 'Q001005');

So, I think the idea is sound in theory. Just need help figuring out how to get that character/string to be understood as a field_name. Anyone have any ideas?

Answer 1

Using execute in plpgsql function. SqlFiddle.

create or replace function show_stats(field_names text[])
returns table ("FieldName" text, "SUM" numeric)
language plpgsql as $$
declare
    fname text;
begin
    foreach fname in array field_names loop
        return query execute format('
            select ''%s''::text, sum(%s) from stats',
            fname, fname);
    end loop;
end $$;

select * from show_stats(array['q001001', 'q001002', 'q001003', 'q001004']);
select * from show_stats(array['q001001', 'q001004']);