How to display & export postgresql output in powershell

Question

How can i display the output of a postgresql in powershell. Here is an example:

$query = "SELECT * FROM test where first_name='test'"

function Get-ODBC-Data{
   param([string]$query=$(throw 'query is required.'))
   $conn = New-Object System.Data.Odbc.OdbcConnection
   $conn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=localhost;Port=5432;Database=Test;Uid=test;Pwd=test;"
   $conn.open()
   $cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
   $ds = New-Object system.Data.DataSet
   (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
   $conn.close()
   $ds.Tables[0]
}

function Set-ODBC-Data{
   param([string]$query=$(throw 'query is required.'))
  $conn = New-Object System.Data.Odbc.OdbcConnection
  $conn.ConnectionString= "Driver={PostgreSQL Unicode(x64)};Server=localhost;Port=5432;Database=test;Uid=test;Pwd=test;"
  $cmd = new-object System.Data.Odbc.OdbcCommand($query,$conn)
  $conn.open()
  $cmd.ExecuteNonQuery()
  $conn.close()

}

$result = Get-ODBC-Data -query $query
$db = set-odbc-data -query $query

How can i display or fetch values present in the output in a format shown in the screenshot?

How can i export the output to a csv in a proper format?

Mostafa Bayomi · Accepted Answer · 2017-02-14 04:53:38Z

0

I didn't test it but try to use below commands as the output from powershell script

C:\Get-Query | Out-GridView
C:\Get-Query | Export-CSV Info.csv
C:\Get-Query | Format-Table -autosize

assuming the script in the file named Get-Query

The solution from this URL

answered Feb 14, 2017 at 4:53

Mostafa Bayomi

131 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

irish Over a year ago

Thanks but this doesnt work. In my code $db value shows the number of results fetched from DB (say 3). what i want is to have powershell show me the output/result in the format shown in my screenshot. I am not sure how to code that part in my script.

Mostafa Bayomi Over a year ago

You can use existing System DSN as in below stackoverflow.com/questions/29930687/…

Collectives™ on Stack Overflow

How to display & export postgresql output in powershell

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related