I was trying to join on information_schema.columns but found out that it cannot be done, and that pg_table_def is the equivalent.
But it has the problem of displaying only the schemas that are present in the search_path, how can I get an information_schema.columns equivalent from pg_table_def
Or set the search_path to search everywhere?
As you are using Amazon Redshift, that information can be derived from svv_column view.
e.g. query
SELECT * FROM svv_columns;
Ref: https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html
This is not affected by search_path and permissions for user running it and is visible to all users.
Step 1: get all the schemas
select nspname FROM pg_namespace
where nspname not like 'pg_%' --to execlude pg schemas unless you need them
and nspname not in (select schemaname from svv_external_schemas); -- to execlude external schemas because you cannot add them to search path
step2: make them into a comma separated list
take the result of the query above and convert to schema1,schema2,schema3,schema4...schema(n)
step3: set your search path
SET search_path to schema1,schema2,schema3,schema4...schema(n)
step 4 pg_table_def
SELECT * FROM pg_table_def
Posting this as an automated solution via SQL and jinja2 templating, this is run in DBT
{% macro populate_schema_catalog(target_schema_name, target_table_name) %}
{%- set fetch_all_schemas -%}
SELECT
nspname
FROM
pg_namespace
WHERE
nspname NOT LIKE 'pg_%'
AND nspname NOT IN (
SELECT
schemaname
FROM
svv_external_schemas
);
{%- endset -%}
{%- set all_schemas = run_query(fetch_all_schemas).rows.values() -%}
{%- set schema_list = [] -%}
-- The schema list now contains all the schemas as such ['"meta"', '"adhoc_dbt"', ...]
{% for schema in all_schemas %}
{%- do schema_list.append('"{}"'.format(schema[0]))-%}
{% endfor %}
-- Set the search_path
{%- set set_search_path -%}
SET search_path TO {{ ', '.join(schema_list) }};
{%- endset -%}
{%- do run_query(set_search_path) -%}
{%- set query_pg_table_def -%}
SELECT
*
FROM
pg_table_def
{%- endset -%}
{%- set all_information = run_query(query_pg_table_def).rows.values() -%}
{% for row in all_information %}
{{ log(row) }}
{% endfor %}
{% endmacro %}