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?

Accost

Great question, especially from a new user. – Casias

Answer 1

Actually I don't know how to specify column names dynamically, but I suggest this way.

SELECT 'q001001' as FieldName, sum(q001001) as SUM FROM stats
UNION SELECT 'q001002' as FieldName, sum(q001002) as SUM FROM stats
UNION SELECT 'q001003' as FieldName, sum(q001003) as SUM FROM stats;

It's easy and would be a solution to your original problem.

Ahola

Yeah, that'll work... will just need to build a bit of logic to assemble the SQL statement properly. Thanks very much! – Accost

@user3648665: It is inefficient to calculate each sum in a separate query. A single scan is substantially cheaper, the difference grows with the number of columns. – Sunnysunproof

Answer 2

Basic query

It's inefficient to calculate each sum separately. Do it in a single SELECT and "cross-tabulate" results.
To keep the answer "short" I reduced to two columns in the result. Expand as needed.

Quick & dirty

Unnest two arrays with equal number of elements in parallel. Details about this technique here and here.

SELECT unnest('{q001001,q001002}'::text[])       AS fieldname
      ,unnest(ARRAY[sum(q001001), sum(q001002)]) AS result
FROM   stats;

"Dirty", because unnesting in parallel is a non-standard Postgres behavior that is frowned upon by some. Works like a charm, though. Follow the links for more.

Verbose & clean

Use a CTE and UNION ALL individual rows:

WITH cte AS (
   SELECT sum(q001001) AS s1
         ,sum(q001002) AS s2
   FROM   stats
   )
SELECT 'q001001'::text AS fieldname, s1 AS result FROM cte
UNION ALL
SELECT 'q001002'::text, s2 FROM cte;

"Clean" because it's purely standard SQL.

Minimalistic

Shortest form, but it's also harder to understand:

SELECT unnest(ARRAY[
          ('q001001', sum(q001001))
         ,('q001002', sum(q001002))])
FROM   stats;

This operates with an array of anonymous records, which are hard to unnest (but possible).

Short

To get individual columns with original types, declare a type in your system:

CREATE TYPE fld_sum AS (fld text, fldsum numeric)

You can do the same for the session temporarily by creating a temp table:

CREATE TEMP TABLE fld_sum (fld text, fldsum numeric);

Then:

SELECT (unnest(ARRAY[
           ('q001001'::text, sum(q001001)::numeric)
          ,('q001002'::text, sum(q001002)::numeric)]::fld_sum[])).*
FROM   stats;

Performance for all four variants is basically the same because the expensive part is the aggregation.
SQL Fiddle demonstrating all variants (based on fiddle provided by @klin).

Automate with PL/pgSQL function

Quick & Dirty

Build and execute code like outlined in the corresponding chapter above.

CREATE OR REPLACE FUNCTION f_list_of_sums1(_tbl regclass, _flds text[])
  RETURNS TABLE (fieldname text, result numeric) AS
$func$
BEGIN

RETURN QUERY EXECUTE (
SELECT '
   SELECT unnest ($1)
         ,unnest (ARRAY[sum(' || array_to_string(_flds, '), sum(')
                              || ')])::numeric
   FROM   ' || _tbl)
USING _flds;

END
$func$  LANGUAGE plpgsql;

• Being "dirty", this is also not safe against SQL injection. Only use it with verified input.
  Below version is safe.

Call:

SELECT * FROM f_list_of_sums1('stats', '{q001001, q001002}');

Verbose & clean

Build and execute code like outlined in the corresponding chapter above.

CREATE OR REPLACE FUNCTION f_list_of_sums2(_tbl regclass, _flds text[])
  RETURNS TABLE (fieldname text, result numeric) AS
$func$
BEGIN

-- RAISE NOTICE '%', (      -- to get debug output uncomment this line ..
RETURN QUERY EXECUTE (      -- .. and comment this one
SELECT 'WITH cte AS (
   SELECT ' || string_agg(
                  format('sum(%I)::numeric AS s%s', _flds[i], i)
                 ,E'\n         ,') || '
   FROM   ' || _tbl || '
   )
' || string_agg(
        format('SELECT %L, s%s FROM cte',  _flds[i], i)
      , E'\nUNION ALL\n')
FROM   generate_subscripts(_flds, 1) i
);

END
$func$  LANGUAGE plpgsql;

Call like above.

Major points

• Implements the efficient code path to aggregate all sums in a single scan.
• Works for any table, not just stats.
• Works for any numeric columns (not just numeric).
• Safe against SQL injection which is a must for dynamic SQL.
  format() and regclass explained:
  Table name as a PostgreSQL function parameter
• About unnesting an array with row numbers:
  PostgreSQL unnest() with element number
• Related answers demonstrating dynamic SQL in plpgsql built with string_agg().

SQL Fiddle demonstrating all variants.

Aside: table definition

The data type numeric(9,0) is a rather inefficient choice for a table definition. Since you are not storing fractional digits and no more than 9 decimal digits, use a plain integer instead. It does the same with only 4 bytes of storage (instead of 8-12 bytes for numeric(9,0)). If you need numeric precision in calculations you can always cast the column at negligible cost.
Also, I don't use varchar(n) unless I have to. Just use text.
So I'd suggest:

CREATE TABLE stats (
   name    text
  ,q001001 int
  ,q001002 int
  , ...
);

Sunnysunproof

If you have a big / varying number of columns, you can build the statement dynamically by looking column names in the system catalog and use EXECUTE in a plpgsql function. You'll find a number of code examples here, try a search. – Sunnysunproof

Answer 3

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']);

Works