Mysql – How to use values from table column as field names in SELECT query

dynamic-sqlMySQLplpgsqlpostgresql

Scenario:

Field names of Table1 named as testtable

id,name,size,width,height

Field names of Table2 named as errortable

id,desc,field1,field2,operator

Values of errortable

+----+-------------------------------------+--------+--------+----------+
| id |                desc                 | field1 | field2 | operator |
+----+-------------------------------------+--------+--------+----------+
|  1 | size should not greater than width  | size   | width  | >        |
|  2 | size should not greater than height | size   | height | >        |
|  3 | with should be equal to height      | width  | height | <>       |
+----+-------------------------------------+--------+--------+----------+

Now I want to check from testtable:

  1. count all those records where size > width.
  2. count all those records where size > height.
  3. count all those records where width <> height.

Required Output

+-------------------------------------+-------+
|              errorname              | count |
+-------------------------------------+-------+
| size should not greater than width  |     6 |
| size should not greater than height |     2 |
| with should be equal to height      |     3 |
+-------------------------------------+-------+

Is it possible to do like this?

Current query:

select desc,
  (select count(*) as "Total Errors" 
   from testtable 
   where errortable.field1 errortable.operator errortable.field2 ) 
from errortable group by id;

Best Answer

Assumptions and Clarification

  • Postgres 9.4
  • You want various counts from the same given table.

desc is a reserved word in SQL, don't use it as identifier. Using descr instead.

1. Basic query

The fastest and most elegant way to get multiple partial counts from the same table is a single SELECT statement with multiple aggregate functions using the FILTER clause:

A statement like:

SELECT count(*) FILTER (WHERE size > width)    AS ct1
     , count(*) FILTER (WHERE size > height)   AS ct2
     , count(*) FILTER (WHERE width <> height) AS ct3
FROM   testtable;

Or, for Postgres 9.3:

SELECT count(size > width OR NULL)    AS ct1
     , ...
FROM   testtable;

2. Advanced query

To get the result in the form you want it, I counter-pivot with a VALUES expression in a LATERAL join, adding the ID and description id, descr in the process:

SELECT x.*
FROM  (
   SELECT count(*) FILTER (WHERE size > width)    AS ct1
        , count(*) FILTER (WHERE size > height)   AS ct2
        , count(*) FILTER (WHERE width <> height) AS ct3
   FROM   testtable
   ) t
, LATERAL (
   VALUES (1, 'size should not greater than width' , ct1)
        , (2, 'size should not greater than height', ct2)
        , (3, 'with should be equal to height'     , ct3)
   ) x(id, descr, ct);

Bold parts come from the errortable when building the statement dynamically:

3. Dynamic query

To concatenate the above statement dynamically from values provided in errortable:

CREATE OR REPLACE FUNCTION
SELECT 'SELECT x.* FROM (SELECT '
    || string_agg(
         format('count(*) FILTER (WHERE %I %s %I) AS c%s'
              , field1, operator, field2, id)
       , ', ')
    || ' FROM testtable) t, LATERAL (VALUES ('
    || string_agg(format('%s, %L, c%s', id, descr, id), '), (')
    || ')) x(id, dscr, ct)'
FROM   errortable;

format() with %I quotes column names where necessary and makes the statement safe against SQL injection - except for operator, which is concatenated as is. You might be able to make that safe, too, with the OPERATOR() construct ...

If untrusted users don't have write access to errortable, you can control its content and need not worry.

4. Full automation

Since the return type is uniform and well known, we can encapsulate it in a function:

CREATE OR REPLACE FUNCTION f_error_count()
  RETURNS TABLE (id int, descr text, ct bigint) AS
$func$
BEGIN

RETURN QUERY EXECUTE (
   SELECT format('SELECT x.* FROM (SELECT %s FROM testtable) t
                        , LATERAL (VALUES (%s)) x(id, dscr, ct)'
               , string_agg(format('count(*) FILTER (WHERE %I %s %I) AS c%s'
                                 , e.field1, e.operator, e.field2, e.id), ', ')
               , string_agg(format('%s, %L, c%s', e.id, e.descr, e.id), '), ('))
   FROM   errortable e
   );

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f_error_count();

Simplifying the concatenation by one more step with an outer format(). Also note how all columns are table-qualified to avoid conflicts.
Again, since dynamic SQL is involved, make sure it cannot be abused for SQL injection.

SQL Fiddle for Postgres 9.3 since 9.4 is not available.

Related Question