Select cell cells as new columns

Question

Basically, I have a table that stores column names with some restrictions: infos and another that stores values ​​for these columns: info_data . I want to get a table with columns from infos and data from info_data . I tried the crosstab function, but it does not 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:

Information about:

 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 CNP 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 CI / BI 0 10 t text 10 serie ci 1 Serie CI / BI 0 30 t text 11 numar ci 1 Numar CI / BI 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 \. 

Question: How can I achieve this result? (columns should be created based on 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) 

Answer 1

This question was much more difficult to solve than you might expect. Your attempt with crosstab() was directed in the right direction. But to assign dynamic column names, you need dynamic SQL: EXECUTE in the plpgsql function.

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

You can simplify the task by avoiding column names that require double quoting. Like nume_anterior instead of "nume anterior" .

You might want to add the row_id column to the row_id table to mark all the elements of the same 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 am synthesizing a missing column with the expression (d.id-1)/13 below - which works for the data in your example.

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

 CREATE EXTENSION tablefunc; 

Find further clarifications and links in this related answer .

This function will do what it is 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(); 

Do not confuse nested dollar-quoting .

BTW: I created a list of columns with this statement:

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

Answer 2

I think 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

since column names are dynamic, you need to use a stored procedure instead of a direct query. In this case, you need to use a dynamic query. Tell me if you can use a stored procedure. I can tell you the exact request.

Answer 3

This ugly beast comes in handy if you need to denormalize EAV material and not have support functions available.

I needed to add a key field for an object 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; 

Answer 4

This is why EAV sucks for most things. If you want to get material in columns, you probably don't want to use EAV. I came up with my own EAV-lite, which uses application data directories and ALTER TABLE commands to create real relational models based on something similar for custom fields. However, it is not quite here or there.

Basically, you have two options. You cannot "just use the stored procedure" because you need to tell PostgreSQL in advance what names and types of results to use. You can use a stored procedure that returns refcursor and dynamic SQL. This is an answer that will satisfy your strict requirements as such.

It will look 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 for you to go.

However, there is another option that you should consider. This does not return in columns, but it is cleaner and not too difficult to parse on the client side. We use it in LedgerSMB, where EAV is really needed.

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

Assuming you don't allow equal characters (you can use any other delimiter), you get a PostgreSQL array that is easily parsed for your application.