Something I didn't go into in the article is that the view I originally wrote was also created dynamically, using the metadata for the tables involved in the join, mostly because I didn't want to have to write the big query because it's not just a matter of writing it once but because it's something that has to be maintained, whereas we only need to call a function to regenerate the view (or the table function) after altering something to regenerate the view (or table function).
I couldn't find a way to do this with CTE's, and the original query needs to return all those columns from the joins anyway.
Would CTEs be a better solution in your case?
SQL 201: Why you should use SQL CTEs
Helen Anderson ・ Oct 20 '18 ・ 3 min read
Here's the official doc for PostgreSQL: postgresql.org/docs/11/queries-wit...
Something I didn't go into in the article is that the view I originally wrote was also created dynamically, using the metadata for the tables involved in the join, mostly because I didn't want to have to write the big query because it's not just a matter of writing it once but because it's something that has to be maintained, whereas we only need to call a function to regenerate the view (or the table function) after altering something to regenerate the view (or table function).
I couldn't find a way to do this with CTE's, and the original query needs to return all those columns from the joins anyway.
Got it, thanks for the explanation! :)