I'm connecting to a PostgreSQL database in Code-igniter and attempting to use the $this->db->field_list()
method to access the field names of my table. The problem is that it seems to be looking across all schema's and pulling fields from multiple tables with the same name in different schema's. The result is a list of unique fields across both tables, which is not what I want.
I suspect that under the hood the field_list()
method is doing this:
SELECT ... FROM information_schema.COLUMNS WHERE table_name = 'my_table'
but unfortunately I have a my_table
in multiple schema's.
I need it do be doing:
SELECT ... FROM information_schema.COLUMNS WHERE table_name = 'my_table' AND table_schema = 'my_schema_1'
I can just query the information schema directly and get the equivalent of this function, but I'm just confused as to why it doesn't recognize the defined schema in my database config file:
$db['default'] = array(
'dsn' => 'pgsql:host=my_host_name;port=5432;dbname=mydb;',
'hostname' => '',
'username' => 'user',
'password' => 'password',
'database' => 'mydb',
'schema' => 'my_schema_1',
'dbdriver' => 'pdo',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => TRUE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
Do I need to define the schema in the DSN or what am I doing wrong here?
I've tried looking for alternative DSN strings online to use, but I don't see one where a schema can be defined for pgSQL.