Score:-3

How to get table names from external DB in non public schema

cn flag

I am writing a configuration entity modul in drupal 9. I want to list all tables, from an external database (postgis) in the schema called gi. The switch between the databases is no problem. I'm searching for a way, to only get the name of the table called gruenzuege to put this in a markup form.

//the form where to save the table

 $form['tables'] = [
      '#type' => 'fieldset',
      '#title' => $this->t('Tables'),
      '#collapsible' => TRUE, // Added
      '#collapsed' => FALSE,  // Added
  ];

//External Database connection

$postgis_database = array(
      'database' => $drugis_postgis_connection->getdatabase(),
      'username' => $drugis_postgis_connection->getusername(),
      'password' => $drugis_postgis_connection->getpassword(),
      'host' => $drugis_postgis_connection->gethost(),
      'driver' => 'pgsql'
  );
Database::addConnectionInfo('postgis', 'default', $postgis_database);
Database::setActiveConnection('default', 'postgis');

//example for fetching table:gruenzuege in drupal

    $results = Database::getConnection('default', 'postgis')->query('SELECT * FROM "gi"."gruenzuege"')->fetchAll();
dpm($results);

I don't understand how to query to another schema, to get only, the table names of the schema gi? It is actually the only table. Thanks for help

cn flag
Welcome to Drupal Answers :) This sounds like a question about SQL rather than Drupal - if not please could you edit and make clear what the Drupal part of it is? Thanks!
Steffen  avatar
cn flag
Sorry, i hope it is clearer now
Score:0
cn flag

listing all table names in an array

$example_database = array(
      'database' => ...,
      'username' => ...,
      'password' => ...,
      'host' => ...,
      'driver' => 'pgsql'
  );

Database::addConnectionInfo('example_key', 'default', $example_database);
Database::setActiveConnection('default', 'example_key');

$result=Database::getConnection('default', 'example_key')->query("SELECT table_name FROM information_schema.tables WHERE table_schema = '" . example_schema . "';");
  $tables = $result->fetchAll();

foreach( $tables as $table ) {
      dpm($table->table_name);
      $tableNames[] = $table->table_name;
  }

Database::setActiveConnection();
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.