0

I am writing queries which read identical tables across databases and schemas and I need the columns to include which schema and table a row comes from.

While searching during the creation of this post I came across this answer

SELECT u.*, n.nspname as schema, u.tableoid::regclass::text as table_or_view
FROM   uses u
JOIN   pg_class     c ON c.oid = u.tableoid
JOIN   pg_namespace n ON c.relnamespace = n.oid

This now includes the schema and the table or view.

What joins do I need now to add the database as well?

2 Answers 2

1

Perhaps this can help:

current_catalogname

current_database ()name

Returns the name of the current database. (Databases are called “catalogs” in the SQL standard, so current_catalog is the standard's spelling.)

1
  • Is there a standard name used by SQL database servers to refer to what PostgreSQL calls a catalog?
    – vfclists
    Commented Feb 6, 2023 at 11:34
1

Get the database name with current_database() like mustaccio provided.

Your column alias "table_or_view" indicates you plan to use this for views also, but VIEWs don't have a tableoid. (Except for MATERIALIZED VIEWs, which are built around actual physical tables as opposed to plain views.)

If you are going to join to pg_class anyway, it makes more sense to output pg_class.relname directly, rather than casting the tableoid:

SELECT current_database() AS db
     , n.nspname          AS schema
     , c.relname          AS table_or_view
     , u.*
FROM   uses u
JOIN   pg_class     c ON c.oid = u.tableoid
JOIN   pg_namespace n ON c.relnamespace = n.oid;

If this is just for tables (and no views), you can use u.tableoid::regclass::text. And you don't need joins to more tables.

The output is only schema-qualified if the current schema search path search_path would require it. To output the schema name always, set an empty search_path for the query. But then you have to schema-qualify your table to find it:

SET search_path =  '';
SELECT current_database() AS db
     , u.tableoid::regclass::text AS schema_and_table
     , *
FROM   public.uses u;  -- schema-qualified table name!

You'll want to keep your current search_path. You could reset it after the query. But rather just set it locally for the query to begin with:

BEGIN;
SET LOCAL search_path = '';  -- SET LOCAL!
SELECT current_database() AS db
     , u.tableoid::regclass::text AS schema_and_table
     , *
FROM   public.uses u;  -- schema-qualified table name!

ROLLBACK;   -- or COMMIT; doesn't matter while you just SELECT;

The manual:

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.