call postgres from powershell

powershell_pgsql

$path="path/to/pgsql/folder"
$ConnectionString = "Server=127.0.0.1;Port=5432;Database=monolith_farm;Uid=postgres;Pwd=root;"


[Reflection.Assembly]::LoadFrom("$path/Npgsql.dll") > $null



function Get-CommandObject {
    param (
        [Parameter(Mandatory=$true)]
        [string] $CommandText,
        [hashtable] $Parameters,
        [Npgsql.NpgsqlConnection] $Connection = $(
    if ($myConnection -ne $null) { $myConnection }
    else { throw New-Object InvalidOperationException }),
        [Npgsql.NpgsqlTransaction] $Transaction = $(
    if ($myTransaction -ne $null) { $myTransaction }
    else { throw New-Object InvalidOperationException })
    )

    $cmd = New-Object Npgsql.NpgsqlCommand ($CommandText, $Connection, $Transaction)

    try {
        if ($Parameters -ne $null -and $Parameters.Count -gt 0) {
            $Parameters.GetEnumerator() | foreach { $key = $_.Key; $value = $_.Value
            if ($value.GetType() -eq @().GetType() -and $value.Length -eq 2 -and $value[1] -is [System.Data.DbType]) {
                Get-ParameterObject $key $value[0] $value[1]
            } else {
                Get-ParameterObject $key $value
            }
            } | foreach { $cmd.Parameters.Add($_) > $null }
        }
    } catch {
        debug-Command $cmd $Parameters
        throw $_.Exception
    }
    $cmd
}


function Invoke-Query {
    param (
        [Parameter(Mandatory=$true)]
        [string] $Query,

        [hashtable] $Parameters,
        [Npgsql.NpgsqlConnection] $Connection = $(
    if ($myConnection -ne $null) { $myConnection }
    else { throw New-Object InvalidOperationException }),
        [Npgsql.NpgsqlTransaction] $Transaction = $(
    if ($myTransaction -ne $null) { $myTransaction }
    else { throw New-Object InvalidOperationException }),
        [switch] $Void,
        [switch] $Scalar,
        [string] $ReturnId
    )

    $cmd = Get-CommandObject -Connection $Connection `
                             -Transaction $Transaction `
                             -CommandText $Query `
                             -Parameters $Parameters

    if ($Debug) {
        write-debug "$(debug-Command $cmd $Parameters)"
    }

    try {
        if ($Void.isPresent) {
            $cmd.ExecuteNonQuery() > $null
            if (![string]::IsNullOrEmpty($ReturnId)) {
                Get-CurrVal $ReturnId $Connection $Transaction
            }
        } elseif ($Scalar.isPresent) {
            $cmd.ExecuteScalar()
        } else {
            $dr = $cmd.ExecuteReader()
            $out = @()
            $fc = $dr.FieldCount

            $fields = 0..($fc - 1) | foreach { $dr.GetName($_) }

            while ($dr.Read()) {
                $row = @{}
                $fields | foreach {
                    $row[$_] = $dr[$_]
                }
                $out += $row
            }
            $out
        }
    } catch {
        Write-Host "Could not invoke command:"
        Write-Host (debug-Command $cmd $Parameters)
        throw
    }
}

$myConnection = new-object Npgsql.NpgsqlConnection $ConnectionString
$myConnection.Open()

$myTransaction =$myConnection.BeginTransaction()

$cnx = Invoke-Query "
select *
from virtual_machine
             " @{
}  $myConnection $myTransaction -ErrorAction stop


$password = 'pwd'
$userid = 'domain/account'
$secstr = New-Object -TypeName System.Security.SecureString
$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$vcenterCred = new-object -typename System.Management.Automation.PSCredential -argumentlist $userid, $secstr
$vcenter = "vcenter001"
Connect-VIServer -Server $vcenter -Credential $vcenterCred > $null

$err = @()


Foreach ($vm in $cnx)
{
    
    Write-Host "Calling VM:"   $vm['vm_name']
    $cmd = @'
curl x
'@
 
      
            $x = invoke-vmscript -VM $vm['vm_name'] -ScriptText $cmd -GuestUser vcenteruser -GuestPassword $password -ScriptType PowerShell -ErrorAction SilentlyContinue
            if( ($x -eq $null) -or ($x.ExitCode -ne 0) )
            {
                Write-Host "cannot contact "  $vm['vm_name']
                $err += $vm['vm_name']
            }
            $x = $null
       
 }

Write-Host "cannot contact these vms"
$myConnection.Close()
Disconnect-VIServer -Server $vcenter