Dynamic view with dynamic list of columns in postgres

Question

I have a scenario as follows:

Table User ID User viewname Description 1 JamesId employeeview This is a employee 2 FredId employeeview This is a employee 3 NickId managerview This is a manager

Table properties ID key value user_ref 1 name james 1 2 phone 88888888888 1 3 email [email protected] 3 4 name fred 2 5 phone 555555555555 2

I need to create views as following:

EmployeeView Id empId name phone Description 1 JamesId james 88888888888 This is a employee 2 FredId fred 555555555555 This is a employee

ManagerView Id empId email Description 1 NickId [email protected] This is a manager

properties table is a extension to User table where everything is stored as key value pair. Views need to be created dynamically using the above two tables. Properties table is dynamic.

Is it possible to generate such view. Can someone give a example to do it.

This is a sample, the properties can grow with different types as key other that email, phone etc. — Adiant
– Adiant, Commented Sep 22, 2015 at 16:25
Is this for Oracle or PostgreSQL? The answer will probably be different for each database. — Jon Heller
– Jon Heller, Commented Sep 22, 2015 at 21:21

Omkar Neogi · Accepted Answer · 2020-01-14 02:35:01Z

I managed to do it with crosstab function in postgres. However i had to modify the properties table to accommodate another colume as a identified.

Modified property table:

+------+-------+---------------+-------------+-------------------+
| **ID |  key  |     value     |    user_ref |   identifier**    |
+------+-------+---------------+-------------+-------------------+
|    1 | name  |    james      |           1 |    viewemployee   |
|    2 | phone |  88888888888  |           1 |    viewemployee   |
|    3 | email | [email protected] |           3 |    viewmanager    |
|    4 | name  |    fred       |           2 |    viewemployee   |
|    5 | phone |  555555555555 |           2 |    viewemployee   |
+------+-------+---------------+-------------+-------------------+

Sql query using crosstab for employeeview:

create MATERIALIZED VIEW EmployeeView as
select one.id, one.userid, two.name, two.phone, one.description from 
user_table one,
(SELECT *
FROM crosstab(
  'SELECT user_ref,key,value FROM user_properties where identifier=''viewemployee'' order by 1,2')
AS
  ct_row (user_ref int, name varchar, phone varchar)) two
where
  one.id=two.user_ref

Sql query using crosstab for managerview:

create MATERIALIZED VIEW ManagerView as
select one.id, one.userid, two.email, one.description from 
user_table one,
(SELECT *
FROM crosstab(
  'SELECT user_ref,key,value FROM user_properties where identifier=''viewmanager'' order by 1,2')
AS
  ct_row (user_ref int, email varchar)) two
where
  one.id=two.user_ref

Collectives™ on Stack Overflow

Dynamic view with dynamic list of columns in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related