Reading table data from PostgreSQL database into Joomla or Wordpress

In this article we discuss the use of the proxy script in order to access tables stored in a remote or local PostgreSQL database. This is particually useful for Wordpress users where built-in support for PostgreSQL is not available.

Just make sure you replace the yourdb with the actual name of your database and the yourpassword with your password. The returned output will be a CSV file that you can consume by a data source of type CSV.

<?php
header('Content-Type: application/csv; charset=utf-8');
header("Content-Disposition: attachment; filename=postgre.csv");
header("Pragma: no-cache");
header("Expires: 0");

function outputCSV($data) {
    $outputBuffer = fopen("php://output", 'w');
    foreach($data as $val) {
        fputcsv($outputBuffer, $val);
    }
    fclose($outputBuffer);
}

// Connecting, selecting database
$dbconn = pg_connect("host=localhost dbname=yourdb user=postgres password=yourpassword")
    or die('Could not connect: ' . pg_last_error());

// Performing SQL query
$query = 'SELECT id,name FROM test';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

if (!empty($result)) {
    $data = array();
    while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {       
        $data[] = $line;       
    }       
    outputCSV($data);   
}

// Free resultset
pg_free_result($result);

// Closing connection
pg_close($dbconn);

exit();
?>