April 2nd, 2014, 02:27 PM
Variable substitution in From clause
I'm trying to use variable substitution in a From clause. I use \set to define a variable.
\set year_suffix 10
I want to select on a table named "trade.imports_ytd10" like this:
Select * from trade.imports_ytd:year_suffix;
But I want to use variable substitution in the middle of a table name. If the table name is "trade.imports_ytd10_sum", I want to do this:
Select * from trade.imports_ytd:year_suffix_sum
I've tried various characters in between year_suffix and _sum (e.g., the concatenation function ||), but no luck.
Anybody have ideas?
April 4th, 2014, 07:29 AM
instead of messing with the symptoms, you should rather fix the underlying problem: Your database design is broken.
Values are stored in rows, not in names. Make a single table for all imports, add a “year” column and then merge the current import tables into your new table. It's probably best to do this with an actual programming language.
As a rule of thumb: When trivial tasks like selecting a bunch of rows require weird workarounds, there's probably something wrong. Stop and investigate. And never hestitate to fix a design mistake. This may take some time, but working around the problem for months or even years takes 10 times as much time.
Comments on this post
April 9th, 2014, 10:18 AM
Thank you for your reply.
I'm not in charge of the database design. I'm just a user of the database. Do you have any ideas about my original question?