pg_query result contains strings instead of integer, numeric

Question 1

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?

Question 2

That's what PHP does. From the manual:

Each value in the array is represented as a string.

Question 3

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)
  RETURNS void AS
$BODY$

declare statement text;
begin

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;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  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. =====

Solution:

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

#.pgpass contents (chmod 600 - requred)    
host:port:db_name:user_name:password

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);
        fclose($stderr);
        if (strlen($error) > 0) {
            throw new Exception("Process error:n$error");
        } else {
            $output = stream_get_contents($stdout);
            fclose($stdout);
            $returnCode = proc_close($process);
            if ($returnCode === -1) {
                throw new Exception("Process was completed incorrectly:n$output");
            } else {
                return array(
                    $returnCode,
                    $output
                );
            }
        }
    }
}

//usage:
$command = sprintf("\copy table(field1, field2) FROM '%s' WITH CSV", $filePath);
executeMetaCommand('postgres', 'test_db', '5432', $command);

Question 4

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.

[EDIT]

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.

[ENDEDIT]

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));

Question 5

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

Try:

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

Edit: or as @ CarlWitthoft suggested in the comments:

matrix(as.numeric(unlist(SFI)),nrow=nrow(SFI))

Question 6

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)) {
    var_dump($row);
}

pg_query result contains strings instead of integer, numeric

Answers

Create function which execute `COPY` command

Give rights on function

Execute from PHP

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

php,postgresql

pg_query result contains strings instead of integer, numeric

Answers

Create function which execute COPY command

Give rights on function

Execute from PHP

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

php,postgresql

Create function which execute `COPY` command