CODEWITHSUNDEEP
postgresqlinformation-schemasearch-path
rob.loh
rob.loh

Reputation: 13

PostgreSQL: SELECT first schema in seach_path which a table name appears in

A query which accomplishes what is in the title would solve my problem.

My problem:

I want to extract column names and data types for a table. Here is my query for this:

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = '<table_name>'
ORDER BY ordinal_position;

This works great, given that the table only occurs once in the whole database. If it occurs in multiple schemas, this query returns all the columns and data types for all tables of that name.

I know that I want only to deal with the first occurrence of this table in the search_path, i.e. the table whose schema appears first in the search_path. How do I extract that information, so I can then set a condition on the table_schema column of information_schema.columns in my query?

Upvotes: 0

Views: 69

Answers (2)

Bergi
Bergi

Reputation: 664185

You can get the schemas from the search_path parameter as an array using the current_schemas() session info function. You could then join this against the information_schema.tables, add your name condition, order by the array index (from UNNEST(current_schemas(true)) WITH ORDINALITY), and LIMIT the result to the first row.

An easier way to resolve a table name using the search path is to just use it, namely by converting the string to the respective table object identifier type. This can be done using a type cast '<table_name>'::regclass or a function call to_regclass('<table_name>') - the latter doesn't throw an error when the table doesn't exist. The result uniquely identifies a specific table in one of the search_path schemas, though getting the schema name from the oid is a bit cumbersome. You could directly query your table columns from the pg_attribute system table instead of information_schema.columns, though that requires you to be aware of things like attisdropped and there's no direct equivalent to data_type.

Instead, you could get the schema name from the pg_class system table:

SELECT column_name, data_type
FROM information_schema.columns
WHERE (table_schema::regnamespace, table_name) = (
  SELECT relnamespace, relname
  FROM pg_class
  WHERE oid = to_regclass('<table_name>')
)
ORDER BY ordinal_position;

or from the pg_identify_object function:

SELECT column_name, data_type
FROM information_schema.columns
WHERE (table_schema, table_name) = (
  SELECT schema, name
  FROM pg_identify_object('pg_class'::regclass, to_regclass('<table_name>'), 0)
)
ORDER BY ordinal_position;

Upvotes: 0

rob.loh
rob.loh

Reputation: 13

Thanks to @Bergi, who commented with the query to get the correct schema name.

Here is the complete query which solves the problem I outlined in the question, using @Bergi's query as a subquery:

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = '<table_name>'
    AND table_schema = (SELECT relnamespace::regnamespace::text FROM pg_class where oid = '<table_name>'::regclass)
ORDER BY ordinal_position;

Upvotes: 0

Related Questions