WorkAround for PHP PDO (with libpq V 9.1.4) to use CITEXT?

Scenario

Two systems (not a server) running PHP and PostgreSQL with the next versions

  • Fedora 15:

    Php

    PHP 5.3.13 (cli) (built: May 9, 2012 2:38:35 PM)
    Copyright (c) 1997-2012 PHP Group
    Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
    Pdo_Pgsql
    PostgreSQL (libpq) version 9.0 .7
    Module version 1.0.2


    PostgreSQL

    PostgreSQL 9.1.4
    with CITEXT extension enabled.

  • Archlinux:

    Php

    PHP 5.4.6 (cli) (: 16 2012 . 12:50:09)
    Copyright (c) 1997-2012 PHP Group
    Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
    Pdo_Pgsql
    PostgreSQL (libpq) 9.1.4
    1.0.2


    PostgreSQL

    PostgreSQL 9.1.4
    CITEXT.


,

select column1 from schema1.table1 where column1= ? 

column1 CITEXT, PHP PDO

  • Fedora PHP 5.3.13, libpq 9.0.7, , , CITEXT ( ).
  • ArchLinux PHP 5.4.6, libpq 9.1.4, , , CITEXT ( ).

, PHP PDO libs - :

select column1 from schema1.table1 where column1= 'value'::text;

.

  • ?
  • ? CITEXT , , PDO.

Update

, ArchLinux PHP 5.4.6, libpq 9.1.4:

LOG:  execute pdo_stmt_00000001: select column1 from schema1.table1 where column1 = $1
DETAIL:  parameters: $1 = 'value'
LOG:  statement: DEALLOCATE pdo_stmt_00000001

column1 VALUE.

0 .

select column1 from schema1.table1 where column1 = 'value';

PSQL .

 column1  
---------
  VALUE
 (1 row)

, ! PDO/postgresql.


2012-08-27 16: 15: 43.669142 + 00 (UMT + 0)

.

, :

try {
    $db = new PDO('pgsql:dbname=database1;user=user;password=pass;host=localhost');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT column1 from schema1.column1 where column1 = 'value'::citext ";
    $retval=$db->query($sql);
    foreach ($retval as $row) {
        print $row['uname'] . '<br>';
    }
}catch (PDOException $PDOerr) {
    echo 'An error occured : <br>';
    var_dump($PDOerr);
    exit;
    //some thing went wrong while performing the action on db.
    }

:

object(PDOException)#10 (8) { ["message":protected]=> string(211) "SQLSTATE[42704]: \
Undefined object: 7 ERROR: type "citext" does not exist LINE 1: ...

, citext ! PSQL, , .


, . , , set search_path - , "$ user", public " . . Daniel VΓ©ritΓ© .

BTW, ALTER ROLE user SET search_path TO "$user",public; . , $user , - , .

+5

All Articles