github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/delegate/show_tables.go (about)

     1  // Copyright 2019 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package delegate
    12  
    13  import (
    14  	"fmt"
    15  
    16  	"github.com/cockroachdb/cockroach/pkg/sql/lex"
    17  	"github.com/cockroachdb/cockroach/pkg/sql/opt/cat"
    18  	"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
    19  	"github.com/cockroachdb/cockroach/pkg/sql/sessiondata"
    20  )
    21  
    22  // delegateShowTables implements SHOW TABLES which returns all the tables.
    23  // Privileges: None.
    24  //   Notes: postgres does not have a SHOW TABLES statement.
    25  //          mysql only returns tables you have privileges on.
    26  func (d *delegator) delegateShowTables(n *tree.ShowTables) (tree.Statement, error) {
    27  	flags := cat.Flags{AvoidDescriptorCaches: true}
    28  	_, name, err := d.catalog.ResolveSchema(d.ctx, flags, &n.ObjectNamePrefix)
    29  	if err != nil {
    30  		return nil, err
    31  	}
    32  
    33  	var schemaClause string
    34  	if n.ExplicitSchema {
    35  		schema := lex.EscapeSQLString(name.Schema())
    36  		if name.Schema() == sessiondata.PgTempSchemaName {
    37  			schema = lex.EscapeSQLString(d.evalCtx.SessionData.SearchPath.GetTemporarySchemaName())
    38  		}
    39  		schemaClause = fmt.Sprintf("AND ns.nspname = %s", schema)
    40  	} else {
    41  		// These must be custom defined until the sql <-> sql/delegate cyclic dependency
    42  		// is resolved. When we have that, we should read the names off "virtualSchemas" instead.
    43  		schemaClause = "AND ns.nspname NOT IN ('information_schema', 'pg_catalog', 'crdb_internal', 'pg_extension')"
    44  	}
    45  
    46  	var query string
    47  	if n.WithComment {
    48  		const getTablesQuery = `
    49  SELECT
    50  	ns.nspname AS schema_name,
    51  	pc.relname AS table_name,
    52  	(CASE
    53  		WHEN pc.relkind = 'v' THEN 'view'
    54  		WHEN pc.relkind = 'S' THEN 'sequence'
    55  		ELSE 'table'
    56  	END) AS "type",
    57    COALESCE(pd.description, '') AS comment
    58   FROM %[1]s.pg_catalog.pg_class       AS pc
    59   JOIN %[1]s.pg_catalog.pg_namespace   AS ns ON (ns.oid = pc.relnamespace)
    60   LEFT JOIN %[1]s.pg_catalog.pg_description AS pd ON (pc.oid = pd.objoid AND pd.objsubid = 0)
    61  WHERE pc.relkind IN ('r', 'v', 'S') %[2]s
    62  ORDER BY schema_name, table_name
    63  `
    64  
    65  		query = fmt.Sprintf(
    66  			getTablesQuery,
    67  			&name.CatalogName,
    68  			schemaClause,
    69  		)
    70  
    71  	} else {
    72  		const getTablesQuery = `
    73  SELECT
    74  	ns.nspname AS schema_name,
    75  	pc.relname AS table_name,
    76  	(CASE
    77  		WHEN pc.relkind = 'v' THEN 'view'
    78  		WHEN pc.relkind = 'S' THEN 'sequence'
    79  		ELSE 'table'
    80  	END) AS "type"
    81   FROM %[1]s.pg_catalog.pg_class       AS pc
    82   JOIN %[1]s.pg_catalog.pg_namespace   AS ns ON (ns.oid = pc.relnamespace)
    83  WHERE pc.relkind IN ('r', 'v', 'S') %[2]s
    84  ORDER BY schema_name, table_name
    85  `
    86  
    87  		query = fmt.Sprintf(
    88  			getTablesQuery,
    89  			&name.CatalogName,
    90  			schemaClause,
    91  		)
    92  	}
    93  
    94  	return parse(query)
    95  }