CodeIgniter - Query in database PostgreSQL (multi database)

Question

CodeIgniter - Query in database PostgreSQL (multi database)

Navigation

#1 by (1 votes)

0

Well, I'm developing an app that lists. The listing is produced by querying a database. The list x is generated only by the query in the database x, the listing y, generated only by the query in the database, and so on. Neither one result should cross, for example, return the listing of X and Y on the same page.

To date, I have been able to return the query in the MySQL database, using the CI classes for the database. However, one of the databases I have to query is PostgreSQL.

In the PostgreSQL database, there are some schemas, which would be divisions to form other "banks".

My question is there, how do I put SET search_path TO blablabla in CI.

NOTE: Queries with MySQL databases are returning results, the only problem is to set search_path .

NOTE: I know that it is possible to perform the procedure with $query = $this->db->query('YOUR QUERY') , but I would like to know if it is possible to use Active records.

NOTE: I have not tried to query() yet to query, but I think I would not have problems returning the result through it, I just wanted to take advantage of the other methods I used for MySQL.

Here's my model currently

class Crud_model extends CI_Model {

    public $banco_mysql;
    public $banco_postgre1;
    public $banco_postgre2;
    public $banco_postgre3;

    public function __construct() {

        parent::__construct();
        $this->banco_mysql = $this->load->database('bd_01', TRUE);
        $this->banco_postgre1 = $this->load->database('bd_02', TRUE);
        $this->banco_postgre2 = $this->load->database('bd_03', TRUE);
        $this->banco_postgre3 = $this->load->database('bd_04', TRUE);
    }

    public function get_name($row) {

        $this->$row['bd']->from($row['path'] . '.' . $row['tabela']);
        $this->$row['bd']->like($row['coluna'], strtoupper($row['name']));

        $result = $this->$row['bd']->get();
        return $result;
    }
}

The data contained in $row['bd'] comes from an array, it contains the name of the database that I want to load. If I leave it that way in the constructor, it will work, at least in MySQL.

    public function __construct() {

        parent::__construct();
        $this->banco_mysql = $this->load->database('bd_01', TRUE);
    }

mvc framework codeigniter

asked by anonymous 28.01.2015 / 12:49

1 answer

Android does not interpret a webpage's "\ n" command Error starting Jboos 7.1 Address already in use: JVM_Bind /0.0.0.0:80

score 1 · Answer 1

Well, looking for some things related to CI and PostgreSQL, I found a guy with a similar question and decided to try something similar to what he did.

For those with an interest, and perhaps the same problem, I solved the problem as follows:

class Crud extends CI_Model {

    public function consulta($var) {

        $this->db->select('title, content, date');
        $this->db->from('search_path.mytable');
        $this->db->like('nome_coluna', $var['nome']);

        $result = $this->db->get();
     }
}

Just put the schema name, or bank, on the line that specifies which table to look for (in this case: $this->db->from('search_path.mytable'); ).

In this way, you can insert the search_path of PostgreSQL and other databases that use the same system.