I have a couple of views in PostgreSQL that return tables like these:
tickets_new
| id | queue | owner | subject | status | created |
| -- | ----- | ----- | -------- | ------ | ------------------- |
| 1 | 1 | 123 | Subject1 | new | 2022-08-22 16:57:26 |
| 2 | 1 | 345 | Subject2 | new | 2022-08-22 13:24:09 |
tickets_handled
| id | queue | owner | subject | priority | status | created |
| -- | ----- | ----- | -------- | -------- | ------ | ------------------- |
| 3 | 4 | 234 | Subject3 | 0 | open | 2022-08-09 16:57:26 |
| 6 | 4 | 45 | Subject6 | 0 | open | 2022-08-13 13:24:09 |
tickets_planworks
| id | subject | status | starts | due |
| -- | ---------- | ------ | ------------------- | ------------------- |
| 12 | Planworks1 | open | 2022-08-23 21:01:00 | 2022-08-23 23:00:00 |
| 20 | Planworks2 | open | 2022-08-23 21:01:00 | 2022-08-23 23:00:00 |
Then there's table objectcustomfieldvalues with structure:
objectcustomfieldvalues
| id | customfield | objectid | content |
| -- | ----------- | -------- | -------------------- |
| 1 | 1 | 3 | Ticket3_Client |
| 2 | 5 | 2 | Ticket2_Interaction |
| 3 | 13 | 6 | Ticket6_Detalisation |
objectid links to id in tickets views
For example I try to join objectcustomfieldvalues with tickets_handled view with this query:
SELECT tt.id, tt.queue, tt.owner, tt.subject, tt.status,
tt.created,
cf_client.content AS client,
cf_interaction.content AS interaction,
cf_detalisation.content AS detalisation
FROM tickets_handled tt
LEFT JOIN
(SELECT objectid, content
FROM objectcustomfieldvalues
WHERE objectid IN (SELECT id FROM tickets_handled)
AND customfield = '1') cf_client
ON cf_client.objectid=tt.id
LEFT JOIN
(SELECT objectid, content
FROM objectcustomfieldvalues
WHERE objectid IN (SELECT id FROM tickets_handled)
AND customfield = '5') cf_interaction
ON cf_interaction.objectid=tt.id
LEFT JOIN
(SELECT objectid, content
FROM objectcustomfieldvalues
WHERE objectid IN (SELECT id FROM tickets_handled)
AND customfield = '13') cf_detalisation
ON cf_detalisation.objectid=tt.id
And it results to table:
tickets_handled
| id | queue | owner | subject | priority | status | created | client | interaction | detalisation |
| -- | ----- | ----- | -------- | -------- | ------ | ------------------- | -------------- | ----------- | -------------------- |
| 3 | 4 | 234 | Subject3 | 0 | open | 2022-08-09 16:57:26 | Ticket3_Client | | |
| 6 | 4 | 45 | Subject6 | 0 | open | 2022-08-13 13:24:09 | | | Ticket6_Detalisation |
I want to have a procedure or somewhat where i can send a variable with view name (eg tickets_handled) and return table with fields from that view plus fields from table objectcustomfieldvalues linked to tickets in view. Now when I write join query I have to mention all the fields for each of my views, also repeat view name in selects and write multiple joins for each customfield joined.