#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2014
    Posts
    2
    Rep Power
    0

    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;

    This works.


    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?

    Thank you.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    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

    • shammat agrees
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2014
    Posts
    2
    Rep Power
    0
    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?

IMN logo majestic logo threadwatch logo seochat tools logo