Viewed   71 times

When I get data from a postgresql database with pg_query,pg_fetch_assoc all numeric fields get returned as strings. Is there a way around this or do I have to typecast every single field the way I need it to be?



That's what PHP does. From the manual:

Each value in the array is represented as a string.

Sunday, November 6, 2022

Thanks to this book

Note that the function presented here effectively by-passes security restrictions, which are there for a reason. Your function should check the file path and table provided against strict white list conditions. This example is also open to SQL injection as it does not quote its input correctly.

Create function which execute COPY command

CREATE OR REPLACE FUNCTION copy_from_csv_ignoring_security(table_name text, table_fieds text, file_path text, oids boolean DEFAULT false, header boolean DEFAULT false, delimeter text DEFAULT ','::text, "null" text DEFAULT ''::text, quote text DEFAULT '"'::text, escape text DEFAULT '"'::text, force_not_null text DEFAULT ''::text)

declare statement text;

statement := 'COPY ' || table_name || ' (' || table_fieds || ') ' || 'FROM ''' || file_path || ''' WITH ';
IF oids THEN
 statement := statement || 'OIDS ';
end if;
statement := statement || 'DELIMITER ''' || delimeter || ''' ';
statement := statement || 'NULL ''' || "null" || ''' CSV ';
IF header THEN
 statement := statement || 'HEADER ';
end if;
statement := statement || 'QUOTE ''' || "quote" || ''' ';
statement := statement || 'ESCAPE ''' || "escape" || ''' ';
IF force_not_null <> '' THEN
statement := statement || 'FORCE NOT NULL ''' || force_not_null || ''' ';
end if;
execute statement;
  COST 100;

Give rights on function

revoke all on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) from public;
grant execute on function copy_from_csv_ignoring_security(text, text, text, boolean, boolean, text, text, text, text, text) to db_user;

Execute from PHP

$dbh->exec('SELECT copy_from_csv_ignoring_security(...)');

===== If version >= 9.1.7 trick above doesn't works. =====


create file .pgpass (avoid password prompt) in home directory of user which run this script.

#.pgpass contents (chmod 600 - requred)    

create php function, which executes meta-command

function executeMetaCommand($dbUser, $dbName, $dbPort, $command)
    $command = sprintf(
        "psql -U %s -p %s -d %s -f - <<EOTn%snEOTn",
        $dbUser, $dbPort, $dbName, $command
    $streams = array(
        array('pipe', 'r'),// stdin
        array('pipe', 'w'),// stdout
        array('pipe', 'w') // stderr
    $process = proc_open($command, $streams, $pipes);
    if (!is_resource($process)) {
        throw new Exception("Cannot open process:n$command");
    } else {
        list(, $stdout, $stderr) = $pipes;
        $error = stream_get_contents($stderr);
        if (strlen($error) > 0) {
            throw new Exception("Process error:n$error");
        } else {
            $output = stream_get_contents($stdout);
            $returnCode = proc_close($process);
            if ($returnCode === -1) {
                throw new Exception("Process was completed incorrectly:n$output");
            } else {
                return array(

$command = sprintf("\copy table(field1, field2) FROM '%s' WITH CSV", $filePath);
executeMetaCommand('postgres', 'test_db', '5432', $command);
Tuesday, November 15, 2022

spoulson has it nearly right, but you need to create a List<string> from string[] first. Actually a List<int> would be better if uid is also int. List<T> supports Contains(). Doing uid.ToString().Contains(string[]) would imply that the uid as a string contains all of the values of the array as a substring??? Even if you did write the extension method the sense of it would be wrong.


Unless you changed it around and wrote it for string[] as Mitch Wheat demonstrates, then you'd just be able to skip the conversion step.


Here is what you want, if you don't do the extension method (unless you already have the collection of potential uids as ints -- then just use List<int>() instead). This uses the chained method syntax, which I think is cleaner, and does the conversion to int to ensure that the query can be used with more providers.

var uids = arrayofuids.Select(id => int.Parse(id)).ToList();

var selected = table.Where(t => uids.Contains(t.uid));
Sunday, November 20, 2022

Edit 2: See @flodel's answer. Much better.


# assuming SFI is your data.frame
as.matrix(sapply(SFI, as.numeric))  

Edit: or as @ CarlWitthoft suggested in the comments:

Tuesday, August 9, 2022

One way (I hope someone will point out a simpler one):

$prepared_statement_name = 'activity1';
$mydbname = '...';

$conn = pg_connect("host=... port=... dbname=... user=... password=...");

$result = pg_query_params($conn, 'SELECT name FROM pg_prepared_statements WHERE name = $1', array($prepared_statement_name));

if (pg_num_rows($result) == 0) {
    $result = pg_prepare($conn, $prepared_statement_name, 'SELECT * FROM pg_stat_activity WHERE datname =  $1');

$result = pg_execute($conn, $prepared_statement_name, array($mydbname));
while($row = pg_fetch_row($result)) {
Wednesday, December 28, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :