Building Dynamic queries Postgres

Question

I have table which stores details of each entity(employee, department). I want to build dynamic query from that table.

CREATE TABLE MyTable
(
    Id int primary key,
    EntityId int,
    ColumnName varchar(100),
    tablename varchar(100)
);

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (1,1,'name','employee');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (2,1,'id','employee');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (3,1,'salary','employee');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (4,2,'name','departement');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (5,2,'location','departement');

INSERT INTO MyTable (Id, EntityId, ColumnName, tableName)
VALUES (6,2,'id','departement');

Above is my table and insert scripts, How can I write a query which gives me output something like below.

SELECT id,name,salary from employee

SELECT id,location,name from departement

If i have multiple entity I should multiple select statements.

This is a known anti-pattern called EAV. Do you really want to go down that road? There are better alternatives in Postgres to do that — user330315
– user330315, Commented Jan 12, 2017 at 13:32
@JakubKania I have posted a dummy schema here, but we do have similar schema.. We are storing this for doing bulk update it selects rows from multiple table requested by user.. apologies for not posting original schema for confidentiality. — Manthan Davda
– Manthan Davda, Commented Jan 12, 2017 at 13:34

Fabian Pijcke · Accepted Answer · 2017-01-12 16:52:28Z

2

If despite the discouraging comments you still want to consider this approach, here is the query that constructs one query per entity:

SELECT entityid,
  'SELECT ' ||
  string_agg(columnname, ', ' ORDER BY id) ||
  ' FROM ' ||
  tablename ||
  ';' AS query
FROM mytable
GROUP BY entityid, tablename;

Result with your example:

 entityid |                   query                   
----------+---------------------------------------------
        1 | SELECT name, id, salary FROM employee;
        2 | SELECT name, location, id FROM departement;
(2 rows)

answered Jan 12, 2017 at 16:52

Fabian Pijcke

3,31027 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building Dynamic queries 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