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?
->condition('i.indrelid', "'semstudent'::regclass")
>condition('i.indrelid', semstudent::regclass)