1

In Powershell, how can I indirectly reference a variable?

I have tables which are referenced in many parts of the code, so I have a list of table name like so:

$xTableName = "Tbl_x"
$yTableName = "Tbl_y"
$zTableName = "Tbl_z"

I have a function to which I want to pass a String representing the table:

function getResult($entityName)
{
$tableName="$" + $entityName + "TableName"
$sqlCommand = "SELECT * FROM " + ${$tableName}
run query etc...
}

I call

getResult("x")

I'm trying to get $sqlCommand = "SELECT * FROM Tbl_x" but get "SELECT * FROM $xTableName"

How do I achieve this?

mklement0
  • 382,024
  • 64
  • 607
  • 775
mikec
  • 155
  • 1
  • 17
  • I think the best the approach would be to store the list of table names in a hash table and lookup that hash table within the `getResult` function. – Abdul Niyas P M Jan 12 '22 at 12:32
  • 1
    See also: [How do I automatically create and use variable names?](https://stackoverflow.com/a/68830451/1701026) – iRon Jan 12 '22 at 14:56
  • 1
    Ultimately, this question is a duplicate of the following posts: https://stackoverflow.com/q/68213804/45375, https://stackoverflow.com/q/53367597/45375, and https://stackoverflow.com/q/42783351/45375 – mklement0 Jan 12 '22 at 15:32
  • 1
    As an aside: `getResult("x")` should be `getResult "x"`: PowerShell functions, cmdlets, scripts, and external programs must be invoked _like shell commands_ - `foo arg1 arg2` - _not_ like C# methods - `foo('arg1', 'arg2')`. If you use `,` to separate arguments, you'll construct an _array_ that a command sees as a _single argument_. See [this answer](https://stackoverflow.com/a/65208621/45375) for more information. – mklement0 Jan 12 '22 at 15:39

2 Answers2

3

You can use Get-Variable -ValueOnly to fetch the value of a variable by name:

$tableName = Get-Variable "${entityName}TableName" -ValueOnly

That being said, a faster and less error prone approach would be to utilize a dictionary to map entity names to table names:

function getResult($entityName)
{
    $tableMapping = @{
        x = "Tbl_x"
        y = "Tbl_y"
        z = "Tbl_z"
    }
    if(-not $tableMapping.ContainsKey($entityName)){
        Write-Error "Invalid entity name '$entityName'"
    }
    else {
        $tableName = $tableMapping[$entityName]
        $sqlCommand = "SELECT * FROM ${$tableName}"
        # execute sql query ...
    }
}
Mathias R. Jessen
  • 157,619
  • 12
  • 148
  • 206
  • https://stackoverflow.com/a/68214332/45375, https://stackoverflow.com/a/53367960/45375 and https://stackoverflow.com/q/42783351/45375 are obvious duplicates to me. Why do you think they're not? – mklement0 Jan 12 '22 at 15:17
  • @mklement0 The dupe target you closed against didn't explain how to use hashtables instead of `Get-Variable` – Mathias R. Jessen Jan 12 '22 at 15:22
  • (I also think it's a bit weird to close as dupe if an answer has been accepted by OP but that's a separate discussion) – Mathias R. Jessen Jan 12 '22 at 15:25
  • Closing duplicates isn't about _alternative solutions_. It's about duplicate questions / problem statements. Your - undoubtedly better - alternative solution (which had my +1) isn't going away when marked as a duplicate - and having links to duplicates can lead future readers to other answers that may have additional background information / answers that are more suited to their particular use cases. – mklement0 Jan 12 '22 at 15:27
  • And, no, I don't think it is weird to close a question with an accepted answer as a duplicate. To me, these aspects are entirely unrelated: If a question is a duplicate, it deserves being linked to duplicate posts, irrespective of what answers, if any, are present, and whether one was accepted or not. – mklement0 Jan 12 '22 at 15:29
  • A modus operandi that makes sense to me: Close obvious duplicates as such, if possible (recognizing/finding them isn't always easy) - ideally before someone answers, but it's also helpful later. Even a closed duplicate can be useful to future readers if its wording helps them discover a solution (possibly via answers given before the closure, if any). If a question is a _near_-duplicate and warrants an answer that covers a specific angle of the larger problem, link to near-duplicate(s) in the answer. – mklement0 Jan 12 '22 at 17:11
  • As for whether your answer falls into the latter category (albeit without linking to near-duplicates): ultimately, it's always a judgment call, but note that the available duplicates listed in my previous comment either _mention_ or _show actual examples_ of hashtable use. (The original duplicate I closed the question with only _mentions_ hashtables (I've since added a link to one of the other two); I was in the process of adding the additional ones when you reopened the question). – mklement0 Jan 12 '22 at 17:11
  • Finally, please note that I'm offering these comments in the spirit of a constructive debate. I certainly try not to wield the "dupe hammer" frivolously or dictatorially. – mklement0 Jan 12 '22 at 17:12
  • Thanks for sharing, at least your point of view is clear :) I don't wholly agree. Dupes are linked to _answers_ of similar/duplicate questions, not the questions themselves, so what you're communicating is "don't worry about any answers to this now-closed post, we got you covered _over here_" - that's appropriate in some cases (like, when dealing with vague questions attracting vague answers). Had this question been "How do I retrieve a variable by name" with no other context, then by all means, dupe-hammer away. In this case, the post provides specific context, and I'm answering as such. – Mathias R. Jessen Jan 12 '22 at 17:25
  • Thanks for responding. I suggest not viewing duplicates that way, especially given that most users looking for a solution won't care, as long as they see a working solution (that's certainly how I view it when I look for solutions). A duplicate is just an acknowledgement that the question has been asked before and that _additional answers_ - which may or may not be more helpful - are available elsewhere (or only there, if no answers were given before closure). In this specific case, to me there's no special angle that isn't also covered by the duplicates, but we can agree to disagree there. – mklement0 Jan 12 '22 at 18:19
  • P.S.: Duplicates are links to _questions_ (posts as a whole), not answers. – mklement0 Jan 12 '22 at 18:21
  • I don't think the posts suggested as duplicates are duplicates of my question. Perhaps it's the terminology being used - indirect referencing/derefencing/dynamic etc. While the solutions may be the same, the question was not. If the question was the same I would have found it and not raised a new post – mikec Jan 13 '22 at 09:14
0

More cleaner way of doing it is to use regular expressions. We can do as below:

$xTableName = "Tbl_x"
$yTableName = "Tbl_y"
$zTableName = "Tbl_z"

function getResult($TableName)
{
    $sqlCommand = "SELECT * FROM tableName" -creplace "tableName","$TableName"
}
getResult($xTableName)
Kushal Solanki
  • 127
  • 1
  • 13
  • You're missing the aspect of referring to a variable _indirectly_. Also, if you're creating the string inside the function you can use simple string concatenation / interpolation - no need for `-creplace`: `$sqlCommand = "SELECT * FROM $TableName"`. Also, it's better not to repeat problematic syntax used in a question: `getResult($xTableName)` should be `getResult $xTableName` – mklement0 Jan 12 '22 at 15:38