Using results from PostgreSQL query in FROM clause of another query

Question

I have a table that's designed as follows.

master_table
id -> serial
timestamp -> timestamp without time zone
fk_slave_id -> integer
fk_id -> id of the table
fk_table1_id -> foreign key relationship with table1
...
fk_table30_id -> foreign key relationship with table30

Every time a new table is added, this table gets altered to include a new column to link. I've been told it was designed as such to allow for deletes in the tables to cascade in the master.

The issue I'm having is finding a proper solution to linking the master table to the other tables. I can do it programmatically using loops and such, but that would be incredibly inefficient.

Here's the query being used to grab the id of the table the id of the row within that table.

SELECT fk_slave_id, concat(fk_table1_id,...,fk_table30_id) AS id
FROM master_table
ORDER BY id DESC
LIMIT 100;

The results are.

 fk_slave_id | id  
-------------+-----
          30 | 678
          25 | 677
          29 | 676
           1 | 675
          15 | 674
           9 | 673

The next step is using this data to formulate the table required to get the required data. For example, data is required from table30 with id 678.

This is where I'm stuck. If I use WITH it doesn't seem to accept the output in the FROM clause.

WITH items AS (
    SELECT fk_slave_id, concat(fk_table1_id,...,fk_table30_id) AS id
    FROM master_table
    ORDER BY id DESC
    LIMIT 100
    )
SELECT data
FROM concat('table', items.fk_slave_id)
WHERE id = items.id;

This produces the following error.

ERROR:  missing FROM-clause entry for table "items"
LINE x: FROM string_agg('table', items.fk_slave_id)

plpgsql is an option to use EXECUTE with format, but then I'd have to loop through each result and process it with EXECUTE.

Is there any way to achieve what I'm after using SQL or is it a matter of needing to do it programmatically?

Apologies on the bad title. I can't think of another way to word this question.

edit 1: Replaced rows with items

edit 2: Based on the responses it doesn't seem like this can be accomplished cleanly. I'll be resorting to creating an additional column and using triggers instead.

"rows" is a reserved word. (look at the syntax highlighting!) Try to avoid reserved words as identifiers, even if they *appear* to work. — wildplasser, Jan 10 '18 at 18:31
@wildplasser good point. I've edited the post (and my script) to remove references to rows. — quickblueblur, Jan 10 '18 at 19:11
Plus: you definitely need dynamic SQL. If table names and/or column **names are based on variables** , it is impossible to create a query plan.You must first create the dynamic query, and feed the result to the parser/planner/engine. — wildplasser, Jan 10 '18 at 19:54

score 0 · Answer 1 · answered Jan 10 '18 at 18:25

0

I don't think you can reference a dynamically named table like that in your FROM clause:

FROM concat('table', rows.fk_slave_id)

Have you tried building/executing that SQL from a stored procedure/function. You can create the SQL you want to execute as a string and then just EXECUTE it.

Take a look at this one:
PostgreSQL - Writing dynamic sql in stored procedure that returns a result set

answered Jan 10 '18 at 18:25

ravioli

3,749
3
14
28

I see that `EXECUTE` is an option, but I'm unsure how I would use it with regards to multiple queries. The linked to topic is returning a result set and not using multiple queries. With `EXECUTE` you use `USING` to insert your value. My value is multiple rows so even if I grab them before using `EXECUTE` I would still need to rely on looping. This is what I can't really see right now. If it's possible to use `EXECUTE` in a manner similar to my `WITH` query. – quickblueblur Jan 10 '18 at 19:17
1

You can use a cursor to generate/execute multiple queries...let me see if I can get an example for you. – ravioli Jan 10 '18 at 19:33
1

Sorry amigo...I don't see a clean way to do this, aside from defining multiple cursors in a function...maybe it's possible, but I give up at this point :) Here are the places I found that were useful: https://stackoverflow.com/questions/756689/postgresql-function-returning-multiple-result-sets https://blog.dsl-platform.com/multiple-result-sets-alternatives-in-postgres-3/ http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure Good luck. – ravioli Jan 10 '18 at 22:37
thank you for taking the time to verify it's not possible to do this nicely. I figured as much, but since I'm far from an SQL expert I wanted to be sure I wasn't missing something. I'll edit the main post, but I'm going to attack this by adding another column and using a trigger on insert to do what I need. – quickblueblur Jan 11 '18 at 17:49
Sure thing :) . – ravioli Jan 11 '18 at 19:20

Using results from PostgreSQL query in FROM clause of another query

1 Answers1