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
– wildplasser, Commented Jan 10, 2018 at 18:31
@wildplasser good point. I've edited the post (and my script) to remove references to rows. — quickblueblur
– quickblueblur, Commented Jan 10, 2018 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
– wildplasser, Commented Jan 10, 2018 at 19:54

ravioli · Accepted Answer · 2018-01-10 18:25:40Z

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, 2018 at 18:25

ravioli

3,8213 gold badges17 silver badges35 bronze badges

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

5 Comments

quickblueblur Over a year ago

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.

ravioli Over a year ago

You can use a cursor to generate/execute multiple queries...let me see if I can get an example for you.

ravioli Over a year ago

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: stackoverflow.com/questions/756689/… blog.dsl-platform.com/… sqlines.com/postgresql/how-to/… Good luck.

quickblueblur Over a year ago

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.

ravioli Over a year ago

Sure thing :) .

Collectives™ on Stack Overflow

Using results from PostgreSQL query in FROM clause of another query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related