[Solved] - [Postgres] Look up columns of tables from app page

Grafana Plugin Development
1

I am struggling to determine how to query a Postgres datasource from a page in an app that I am developing. I basically need to do tag mapping on the page and I want to query Postgres for a list of all available tags. I am referencing this ticket but do not know where to go to learn what functions are available for doing the actual querying.

2

In my digging I discovered this and I believe that I could use it to accomplish my needs. The only piece I’m not certain about is the data object and what are the expected formats, types, etc.

this.backendSrv
  .datasourceRequest({
    url: '/api/tsdb/query',
    method: 'POST',
    data: {
      from: options.range.from.valueOf().toString(),
      to: options.range.to.valueOf().toString(),
      queries: queries,
     },
   })
      .then(this.responseParser.processQueryResult);
  }

Am I on the right track?

3

I realize now that my most recent comment will not work. That datasource query will return an object intended for painting to the graph. I suspect that if I were to send a query asking for just metadata it would break the query.

4

Well, I was able to figure it out. In case anyone ever comes along wondering how to do the same here is how I did it:

  1. Load your datasource as explained in the post that I linked to. Mine looked like this for Postgres:
  loadDatasource(id) {
    return this.backendSrv.get('api/datasources/' + id)
      .then(ds => {
        this.datasource = ds.jsonData.ds;
        return this.datasourceSrv.get(ds.name);
      }).then(psqlDS => {
        this.psqlDS = psqlDS;
        return psqlDS;
      });
  }
  1. Then you can use a function available in the Postgres Datasource called “metricFindQuery” to send any query you want to your Postgres Datasource. The digging I had to do took me to public/app/plugins/datasource/postgres/datasource.ts for learning more about the Postgres Datasource. And here is my code snippet:
this.loadDatasource().then(() => {
      this.psqlDS.metricFindQuery(
        "SELECT column_name, 
                table_name 
         FROM information.schema.columns
         WHERE table_name 
            IN (SELECT tableName 
                  FROM pg_catalog.pg_tables 
                  WHERE schemaname='public')")
        .then(resp => {
        this.test = resp
      })

I hope this helps someone else down the road!!