PowerShell : how do I indirectly reference a variable

Question

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?

Answer 1

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 ...
    }
}

Answer 2

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)