Select row cells as new columns

Question

Basically, I have a table that stores column names with some restrictions: infos, and another one that stores the values for those columns: info_data. I want to get a table which has the columns from infos and data from info_data. I've tried with crosstab function but it doesn't have the desired effect.

I have 2 tables:

CREATE TABLE infos
(id serial PRIMARY KEY,
 name text NOT NULL,
 id_member integer NOT NULL,
 title text,
 min_length integer NOT NULL DEFAULT 0,
 max_length integer NOT NULL DEFAULT 30,
 required boolean NOT NULL DEFAULT false,
 type text NOT NULL DEFAULT 'text'::text
);

CREATE INDEX info_id_idx ON infos (id);

and

CREATE TABLE info_data
(id serial PRIMARY KEY,
 id_info integer,
 value text,
  CONSTRAINT info_data_id_info_fkey FOREIGN KEY (id_info)
      REFERENCES infos (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE INDEX info_data_id_idx ON info_data(id);

with the following values:

infos:

COPY infos (id, name, id_member, title, min_length, max_length, required, type)     FROM     stdin;
1 nume 1 Nume 0 30 t text
2 prenume 1 Prenume 0 30 t text
3 cnp 1 C.N.P. 13 13 t number
4 nume anterior 1 Nume anterior 0 30 f text
5 stare civila 1 Starea civila 0 30 f text
6 cetatenie 1 Cetatenie 0 30 f text
7 rezidenta 1 Rezidenta 0 30 f text
9 tip act 1 C.I. / B.I. 0 10 t text
10 serie ci 1 Serie C.I. / B.I. 0 30 t text
11 numar ci 1 Numar C.I. / B.I. 0 30 t text
12 data eliberarii 1 Data eliberarii 0 30 t text
13 eliberat de 1 Eliberat de 0 30 t text
8 adresa 1 Adresa 0 50 f text
\.

info_data:

COPY info_data (id, id_info, value) FROM stdin;
1 1 a
2 2 a
3 3 100
4 4
5 5
6 6
7 7
8 8
9 9 ci
10 10 sv
11 11 13
12 12 132
13 13 123
14 1 b
15 2 b
16 3 100
17 4
18 5
19 6
20 7
21 8
22 9 BI
23 10 XT
24 11 123
25 12 10
26 13 10
\.

The question: How can I achieve this output? (the columns have to be generated based upon the unique entries from the infos table

nume, prenume, cnp, nume anterior, ... (as columns - built from infos)
a   , a, ...
b   , b, ... (as rows - built from info_data)

Tanganyika

Your table has the name infos (plural), yet the sequence is named info_id_seq (singular) and the pk info_pkey? I assumed typos and simplified your question accordingly. – Jubal

The info_data should have an extra key column to designate the entity that it belongs to. – Pacificas

Answer 1

This question was a lot harder to solve than you may have expected. Your attempt with crosstab() was aiming in the right direction. But to assign dynamic column names you need dynamic SQL in addition: EXECUTE in a plpgsql function.

Change the data type of the column infos.type from text to regtype to prevent SQL injection and other errors. For instance, you have the data type number, which is not a valid PostgreSQL data type. I replaced it with numeric, so it can work.

You could simplify the task by avoiding column names that need double-quoting. Like nume_anterior instead of "nume anterior".

You might want to add a column row_id to your table info_data to mark all elements of one row. You need it for the crosstab() function, and it allows you to ignore columns with NULL values. The crosstab() function with two parameters can deal with missing columns. I synthesize the missing column with the expression (d.id-1)/13 below - which works for the data in your example.

You need to install the additional module tablefunc (once per database):

CREATE EXTENSION tablefunc;

Find additional explanation and links in this related answer.

This function will do what are looking for:

CREATE OR REPLACE FUNCTION f_mytbl()
  RETURNS TABLE (id int
, nume text           , prenume text       , cnp numeric
, "nume anterior" text, "stare civila" text, cetatenie text
, rezidenta text      , adresa text        , "tip act" text
, "serie ci" text     , "numar ci" text    , "data eliberarii" text
, "eliberat de" text)
  LANGUAGE plpgsql AS
$BODY$
BEGIN

RETURN QUERY EXECUTE $f$
SELECT *
FROM   crosstab(
    'SELECT (d.id-1)/13 -- AS row_id
          , i.id, d.value
     FROM   infos i
     JOIN   info_data d ON d.id_info = i.id
     ORDER  BY 1, i.id',

    'SELECT id
     FROM   infos
     ORDER  BY id'
    )
AS tbl ($f$ || 'id int,
, nume text           , prenume text       , cnp numeric
, "nume anterior" text, "stare civila" text, cetatenie text
, rezidenta text      , adresa text        , "tip act" text
, "serie ci" text     , "numar ci" text    , "data eliberarii" text
, "eliberat de" text)';

END;
$BODY$;

Call:

SELECT * FROM x.mytbl();

Don't get confused by the nested dollar-quoting.

BTW: I created the column list with this statement:

SELECT 'id int,' || string_agg(quote_ident(name) || ' ' || type
                              ,', ' ORDER BY i.id) 
FROM   infos i;

Jubal

Answer 2

I guess you can do something like this

SELECT 
    C1.VALUE AS (SELECT NAME FROM INFOS WHERE ID=1),  
    C2.VALUE AS (SELECT NAME FROM INFOS WHERE ID=2),  
    C3.VALUE AS (SELECT NAME FROM INFOS WHERE ID=3),  
    C4.VALUE AS (SELECT NAME FROM INFOS WHERE ID=4),  
    C5.VALUE AS (SELECT NAME FROM INFOS WHERE ID=5),  
    C6.VALUE AS (SELECT NAME FROM INFOS WHERE ID=6),  
    C7.VALUE AS (SELECT NAME FROM INFOS WHERE ID=7),  
    C8.VALUE AS (SELECT NAME FROM INFOS WHERE ID=8),  
    C9.VALUE AS (SELECT NAME FROM INFOS WHERE ID=9),  
    C10.VALUE AS (SELECT NAME FROM INFOS WHERE ID=10),  
    C11.VALUE AS (SELECT NAME FROM INFOS WHERE ID=11),  
    C12.VALUE AS (SELECT NAME FROM INFOS WHERE ID=12),  
    C13.VALUE AS (SELECT NAME FROM INFOS WHERE ID=13)  
    FROM (
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=1) C1,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=2) C2,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=3) C3,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=4) C4,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=5) C5,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=6) C6,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=7) C7,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=8) C8,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=9) C9,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=10) C10,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=11) C11,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=12) C12,
    (SELECT VALUE FROM INFO_DATA WHERE ID_INFO=13) C13)

This is only when the number of rows in your Infos table is 13.

EDIT

as the column names are dynamic, you need to use stored procedure in place of direct query.In that case you have to do use dynamic query. Tell me if you can use stored procedure. I can tell exact query.

Onaonager

This answer is just wrong. You cannot assign names dynamically in plain SQL. You need dynamic SQL - in a plpgsql function, for instance.. – Jubal

Answer 3

This ugly beast comes in handy if you have to denormalise EAV-stuff and don't have pivot-like functions available.

I had to add a keyfield for the entity in info_data.

ALTER TABLE info_data
        ADD column id_key INTEGER
        ;
UPDATE info_data
SET id_key= 1+ ((id-1)/13);

WITH reut AS (
   SELECT d.id_info
        , d.value
        , d.id_key
        , i.zname AS zname
   FROM info_data d
   JOIN infos i ON i.id = d.id_info
        )
, num AS (
        SELECT DISTINCT id_key AS id_key
        FROM info_data
        )
SELECT n.id_key AS id_key
        ,r1.value AS "nume"
        ,r2.value AS "prenume"
        ,r3.value AS "cnp"
        ,r4.value AS "nume anterior"
        ,r5.value AS "stare civila"
        ,r6.value AS "cetatenie"
        ,r7.value AS "rezidenta"
        ,r8.value AS "adresa"
        ,r9.value AS "tip act"
        ,r10.value AS "serie ci"
        ,r11.value AS "numar ci"
        ,r12.value AS "data eliberarii"
        ,r13.value AS "eliberat de"
FROM num n
LEFT JOIN reut r1 ON r1.id_key = n.id_key AND r1.zname = 'nume'
LEFT JOIN reut r2 ON r2.id_key = n.id_key AND r2.zname = 'prenume'
LEFT JOIN reut r3 ON r3.id_key = n.id_key AND r3.zname = 'cnp'
LEFT JOIN reut r4 ON r4.id_key = n.id_key AND r4.zname = 'nume anterior'
LEFT JOIN reut r5 ON r5.id_key = n.id_key AND r5.zname = 'stare civila'
LEFT JOIN reut r6 ON r6.id_key = n.id_key AND r6.zname = 'cetatenie'
LEFT JOIN reut r7 ON r7.id_key = n.id_key AND r7.zname = 'rezidenta'
LEFT JOIN reut r8 ON r8.id_key = n.id_key AND r8.zname = 'adresa'
LEFT JOIN reut r9 ON r9.id_key = n.id_key AND r9.zname = 'tip act'
LEFT JOIN reut r10 ON r10.id_key = n.id_key AND r10.zname = 'serie ci'
LEFT JOIN reut r11 ON r11.id_key = n.id_key AND r11.zname = 'numar ci'
LEFT JOIN reut r12 ON r12.id_key = n.id_key AND r12.zname = 'data eliberarii'
LEFT JOIN reut r13 ON r13.id_key = n.id_key AND r13.zname = 'eliberat de'

ORDER BY n.id_key;

Pacificas

Answer 4

This is why EAV sucks for most things. If you want to retrieve stuff in columns, you probably don't want to use EAV. I have come up with my own EAV-lite which uses application data catalogs and ALTER TABLE commands to create actual relational models based on something similar for custom fields. However that's not really here or there.

Basically you have two options. You can't "just use a stored procedure" because you have to tell PostgreSQL in advance what result names and types to use. You can use a stored procedure that returns a refcursor and dynamic SQL. That's the answer that will satisfy your strict requirements as such.

That will look something like

CREATE OR REPLACE FUNCTION eav_get(in_id int) RETURNS refcursor
LANGUAGE PLPGSQL AS 
 $$ DECLARE outval refcursor;
            t_row RECORD;
            t_query TEXT;
  BEGIN
      t_query := 'SELECT ';

      FOR t_row IN select distinct "name" FROM infos
      LOOP
         t_query := t_query 'max(CASE WHEN "name" = '|| quote_literal(t_row."name") || ' THEN value ELSE NULL END) ';
      END LOOP;
      t_query := t_query || 'FROM info_data WHERE id_key = || in_id || ' 
                 GROUP BY id_key ';
      OPEN outval FOR EXECUTE t_query;
      RETURN outval;
 END; $$;

I have not tested the above code but it should be close enough t get you going.

However, there is another option you should consider. This doesn't return in columns but it is cleaner and not too hard to parse on the client side. We use it in LedgerSMB where EAV is actually needed.

 SELECT id_key, array_agg("name"::text || '=' || "value"::text) 
   FROM infos_data 
  WHERE id_key = ?

Assuming you don't allow equal signs (you could use any other separator) you get a PostgreSQL array back that's easy to parse for your application.

Devol