If you have not installed the additional module tablefunc, run this command once per database:
CREATE EXTENSION tablefunc;
Answer to question
A very basic crosstab solution for your case:
SELECT * FROM crosstab(
'SELECT bar, 1 AS cat, feh
FROM tbl_org
ORDER BY bar, feh')
AS ct (bar text, val1 int, val2 int, val3 int); -- more columns?
The special difficulty here is, that there is no category (cat) in the base table. For the basic 1-parameter form we can just provide a dummy column with a dummy value serving as category. The value is ignored anyway.
This is one of the rare cases where the second parameter for the crosstab() function is not needed, because all NULL values only appear in dangling columns to the right by definition of this problem. And the order can be determined by the value.
If we had an actual category column with names determining the order of values in the result, we'd need the 2-parameter form of crosstab(). Here I synthesize a category column with the help of the window function row_number(), to base crosstab() on:
SELECT * FROM crosstab(
$$
SELECT bar, val, feh
FROM (
SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
FROM tbl_org
) x
ORDER BY 1, 2
$$
, $$VALUES ('val1'), ('val2'), ('val3')$$ -- more columns?
) AS ct (bar text, val1 int, val2 int, val3 int); -- more columns?
The rest is pretty much run-of-the-mill. Find more explanation and links in these closely related answers.
Basics:
Read this first if you are not familiar with the crosstab() function!
Advanced:
Proper test setup
The test setup that's missing in the question:
CREATE TEMP TABLE tbl_org (id int, feh int, bar text);
INSERT INTO tbl_org (id, feh, bar) VALUES
(1, 10, 'A')
, (2, 20, 'A')
, (3, 3, 'B')
, (4, 4, 'B')
, (5, 5, 'C')
, (6, 6, 'D')
, (7, 7, 'D')
, (8, 8, 'D')
;
Dynamic crosstab?
Not very dynamic, yet, as @Clodoaldo commented. Dynamic return types are hard to achieve with plpgsql. But there are ways around it - with some limitations.
So not to further complicate the rest, I demonstrate with a simpler test case:
CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
('A', 'val1', 10)
, ('A', 'val2', 20)
, ('B', 'val1', 3)
, ('B', 'val2', 4)
, ('C', 'val1', 5)
, ('D', 'val3', 8)
, ('D', 'val1', 6)
, ('D', 'val2', 7)
;
Call:
SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2')
AS ct (row_name text, val1 int, val2 int, val3 int);
Returns:
row_name | val1 | val2 | val3
----------+------+------+------
A | 10 | 20 |
B | 3 | 4 |
C | 5 | |
D | 6 | 7 | 8
Built-in feature of tablefunc module
The tablefunc module provides a simple infrastructure for generic crosstab() calls without providing a column definition list. A number of functions written in C (very fast):
crosstabN()
crosstab1() - crosstab4() are pre-defined. One minor point: they require and return all text. So we need to cast our integer values. But it simplifies the call:
SELECT * FROM crosstab4('SELECT row_name, attrib, val::text -- cast!
FROM tbl ORDER BY 1,2')
Result:
| row_name |
category_1 |
category_2 |
category_3 |
category_4 |
| A |
10 |
20 |
|
|
| B |
3 |
4 |
|
|
| C |
5 |
|
|
|
| D |
6 |
7 |
8 |
|
Custom crosstab() function
For more columns or other data types, we create our own composite type and function (once).
Type:
CREATE TYPE tablefunc_crosstab_int_5 AS (
row_name text, val1 int, val2 int, val3 int, val4 int, val5 int);
Function:
CREATE OR REPLACE FUNCTION crosstab_int_5(text)
RETURNS SETOF tablefunc_crosstab_int_5
AS '$libdir/tablefunc', 'crosstab' LANGUAGE c STABLE STRICT;
Call:
SELECT * FROM crosstab_int_5('SELECT row_name, attrib, val -- no cast!
FROM tbl ORDER BY 1,2');
Result:
| row_name |
val1 |
val2 |
val3 |
val4 |
val5 |
| A |
10 |
20 |
|
|
|
| B |
3 |
4 |
|
|
|
| C |
5 |
|
|
|
|
| D |
6 |
7 |
8 |
|
|
One polymorphic, dynamic function for all
This goes beyond what's covered by the tablefunc module.
To make the return type dynamic I use a polymorphic type with a technique detailed in this related answer:
1-parameter form:
CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _rowtype anyelement)
RETURNS SETOF anyelement
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE
(SELECT format('SELECT * FROM crosstab(%L) t(%s)'
, _qry
, string_agg(quote_ident(attname) || ' ' || atttypid::regtype
, ', ' ORDER BY attnum))
FROM pg_attribute
WHERE attrelid = pg_typeof(_rowtype)::text::regclass
AND attnum > 0
AND NOT attisdropped);
END
$func$;
Overload with this variant for the 2-parameter form:
CREATE OR REPLACE FUNCTION crosstab_n(_qry text, _cat_qry text, _rowtype anyelement)
RETURNS SETOF anyelement
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE
(SELECT format('SELECT * FROM crosstab(%L, %L) t(%s)'
, _qry, _cat_qry
, string_agg(quote_ident(attname) || ' ' || atttypid::regtype
, ', ' ORDER BY attnum))
FROM pg_attribute
WHERE attrelid = pg_typeof(_rowtype)::text::regclass
AND attnum > 0
AND NOT attisdropped);
END
$func$;
pg_typeof(_rowtype)::text::regclass: There is a row type defined for every user-defined composite type, so that attributes (columns) are listed in the system catalog pg_attribute. The fast lane to get it: cast the registered type (regtype) to text and cast this text to regclass.
Create composite types once
You need to define once every return type you are going to use:
CREATE TYPE tablefunc_crosstab_int_3 AS (
row_name text, val1 int, val2 int, val3 int);
CREATE TYPE tablefunc_crosstab_int_4 AS (
row_name text, val1 int, val2 int, val3 int, val4 int);
...
For ad-hoc calls, you can also just create a temporary table to the same (temporary) effect:
CREATE TEMP TABLE temp_xtype7 AS (
row_name text, x1 int, x2 int, x3 int, x4 int, x5 int, x6 int, x7 int);
Or use the type of an existing table, view or materialized view if available.
Call
Using above row types:
1-parameter form (no missing values):
SELECT * FROM crosstab_n(
'SELECT row_name, attrib, val FROM tbl ORDER BY 1,2'
, NULL::tablefunc_crosstab_int_3);
2-parameter form (some values can be missing):
SELECT * FROM crosstab_n(
'SELECT row_name, attrib, val FROM tbl ORDER BY 1'
, $$VALUES ('val1'), ('val2'), ('val3')$$
, NULL::tablefunc_crosstab_int_3);
This one function works for all return types, while the crosstabN() framework provided by the tablefunc module needs a separate function for each.
If you have named your types in sequence like demonstrated above, you only have to replace the bold number. To find the maximum number of categories in the base table:
SELECT max(count(*)) OVER () FROM tbl -- returns 3
GROUP BY row_name
LIMIT 1;
That's about as dynamic as this gets if you want individual columns. Arrays like demonstrated by @Clocoaldo or a simple text representation or the result wrapped in a document type like json or hstore can work for any number of categories dynamically.
Disclaimer:
It's always potentially dangerous when user input is converted to code. Make sure this cannot be used for SQL injection. Don't accept input from untrusted users (directly).
Call for original question:
SELECT * FROM crosstab_n('SELECT bar, 1, feh FROM tbl_org ORDER BY 1,2'
, NULL::tablefunc_crosstab_int_3);
EXECUTEwith theformatfunction to generate the CASE statements dynamically. Daniel is right that it's probably better to just usecrosstabthough. – Ninocrosstab(). – Highlight