Retrieving the primary Key column

Question

I have been using this query to query Postgres Table Primary Key Column.

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'semstudent'::regclass
AND    i.indisprimary;

I want to perform the above query in Drupal. Below is my code:

$query=db_select('pg_index', 'i',array('target' => 'import'));  
$query->join('pg_attribute' , 'a' ,'a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)');                 
$result = $query
    ->fields('a', array('attname','atttypid','atttypmod'))  
    ->condition('i.indrelid', '"semstudent"::regclass')     
    ->condition('i.indisprimary', true) 
    ->execute();

The query above create the error below:

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type oid: ""semstudent"::regclass" LINE 5: WHERE (i.indrelid = '"semstudent"::regclass') AND (i.indisp... ^: SELECT a.attname AS attname, a.atttypid AS atttypid, a.atttypmod AS atttypmod FROM {pg_index} i INNER JOIN {pg_attribute} a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE (i.indrelid = :db_condition_placeholder_0) AND (i.indisprimary = :db_condition_placeholder_1) ; Array ( [:db_condition_placeholder_0] => "semstudent"::regclass [:db_condition_placeholder_1] => 1 ) in myid_get_template_table_primary_key() (line 755 of C:\xampp\htdocs\drupal- 7.34\sites\all\modules\myid\includes\myid_inputting.inc).

I guess it has something to do with the statement below.

->condition('i.indrelid', '"semstudent"::regclass') 

How do I convert it?

UPDATE:

Suggestions below are appreciated but it doesnt make any changes in my code. I have somehow managed to get it working manually. See code below:

 $database = 'import';
 $table = 'semstudent';

 $query=db_select('pg_index', 't1',array('target' => $database));
 $query->join('pg_attribute' , 't2' ,'t2.attrelid = t1.indrelid AND t2.attnum = ANY(t1.indkey)');                   
 $result = $query
    ->fields('t2', array('attname','atttypid','atttypmod'))  
    ->condition('t1.indrelid', myid_get_table_oid($table, $database)) 
    ->condition('t1.indisprimary', true) 
    ->execute();

I have made the function myid_get_table_oid supplied with two parameters: $table and $database. $table represents the name of the the table. $database represents the target database.

//A function that returns the oid of a particular table.
function myid_get_table_oid($table_name, $database){

    $query=db_select('pg_class ', 't1',array('target' => $database));                       
    $result = $query
        ->fields('t1', array('relfilenode'))  
        ->condition('t1.relname', $table_name)          
        ->execute();

    $oid = '';
    foreach($result as $record){     
        $oid = $record->relfilenode;
        break;
    }       
    return $oid;
}

In many instances, the code I made is working but there is one scenario the function returns no primary key. When I use the code above with a particular table myid_template_27 in my database. The function myid_get_table_oid returns an oid 8948915. But the converted query doesnt return anything.

I am pretty sure that the table myid_template_27 have a primary key column. It must return the column biginta.See image below:

To see if biginta really exist on pg_attribute table, I made a query below:

SELECT t1.indrelid, t2.attname AS attname, t2.atttypid AS atttypid, t2.atttypmod       
AS atttypmod
FROM pg_index t1
INNER JOIN pg_attribute t2 ON t2.attrelid = t1.indrelid AND t2.attnum =     ANY(t1.indkey)
WHERE  (t1.indisprimary = true)  AND (t2.attname = 'biginta')

The result shows a different indrelid oid which is different from the value returned by my function myid_get_table_oid.

Where did I go wrong?

Answer 1

As some people already mentioned and the error itself describes, this is a variable interpolation/auto-quoting problem.

The most appropriate option here - following the D.O.'s documentation - would be to use where(), instead of condition().

$query->where($snippet, $args = array())

The where() method allows for the addition of arbitrary SQL as a conditional fragment. $snippet may contain any legal SQL fragment, and if it has variable content it must be added using a named placeholder. The $args array is an array of placeholders and values that will be substituted into the snippet. It is up to the developer to ensure that the snippet is valid SQL. No database-specific modifications are made to the snippet.

(Copied from https://www.drupal.org/node/310086)

And here is a rewrite of your code:

$query=db_select('pg_index', 'i',array('target' => 'import'));  
$query->join('pg_attribute' , 'a' ,'a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)');                 
$result = $query
    ->fields('a', array('attname','atttypid','atttypmod'))  
    ->where("i.indrelid = 'semstudent'::regclass")
    ->condition('i.indisprimary', true) 
    ->execute();

I just tried this and it worked without any problems, hope it works for you too!

Answer 2

Have you tried db_query It will be clear to write the same query with minimal edit.

 db_query('SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
    FROM   pg_index i
    JOIN   pg_attribute a ON a.attrelid = i.indrelid
                         AND a.attnum = ANY(i.indkey)
    WHERE  i.indrelid = :tablename::regclass
    AND    i.indisprimary', array(':tablename' => $tablename));