Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
---- this query si meant to return something similar to information_schema.view_column_usage
---- but only the columns that are actually in the SELECT part of the result
---- and the name of the column as used in the view definition
-- list only the columns from views
WITH view_columns AS (
SELECT
c.oid AS view_oid,
a.attname::information_schema.sql_identifier AS column_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace nc ON c.relnamespace = nc.oid
WHERE
NOT pg_is_other_temp_schema(nc.oid)
AND a.attnum > 0
AND NOT a.attisdropped
AND (c.relkind = 'v'::"char")
AND nc.nspname NOT IN ('information_schema', 'pg_catalog')
),
-- list the source tables/columns used by each view (including the ones used just in the WHERE section)
view_column_usage AS (
SELECT DISTINCT
v.oid as view_oid,
nv.nspname::information_schema.sql_identifier AS view_schema,
v.relname::information_schema.sql_identifier AS view_name,
nt.nspname::information_schema.sql_identifier AS table_schema,
t.relname::information_schema.sql_identifier AS table_name,
a.attname::information_schema.sql_identifier AS column_name,
pg_get_viewdef(v.oid)::information_schema.character_data AS view_definition
FROM pg_namespace nv
JOIN pg_class v ON nv.oid = v.relnamespace
JOIN pg_depend dv ON v.oid = dv.refobjid
JOIN pg_depend dt ON dv.objid = dt.objid
JOIN pg_class t ON dt.refobjid = t.oid
JOIN pg_namespace nt ON t.relnamespace = nt.oid
JOIN pg_attribute a ON t.oid = a.attrelid AND dt.refobjsubid = a.attnum
WHERE
nv.nspname not in ('information_schema', 'pg_catalog')
AND v.relkind = 'v'::"char"
AND dv.refclassid = 'pg_class'::regclass::oid
AND dv.classid = 'pg_rewrite'::regclass::oid
AND dv.deptype = 'i'::"char"
AND dv.refobjid <> dt.refobjid
AND dt.classid = 'pg_rewrite'::regclass::oid
AND dt.refclassid = 'pg_class'::regclass::oid
AND (t.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"]))
),
-- using the name of the source schema/table/column and the view definition
-- try to detect if that column appears in the SELECT part of the view
-- the regexp is aided a bit by the fact that the view definition returned by pg_get_viewdef
-- is cleanded up a bit compared to how it was inputed
candidates AS (
SELECT
vcu.*,
(
SELECT CASE WHEN match IS NOT NULL THEN coalesce(match[7], match[4]) END
FROM REGEXP_MATCHES(
CONCAT('SELECT ', SPLIT_PART(vcu.view_definition, 'SELECT', 2)),
CONCAT('SELECT.*?((',vcu.table_name,')|(\w+))\.(', vcu.column_name, ')(\sAS\s(")?([^"]+)\6)?.*?FROM.*?',vcu.table_schema,'\.(\2|',vcu.table_name,'\s+(AS\s)?\3)'),
'ns'
) match
) AS view_column_name
FROM view_column_usage AS vcu
)
-- theoretically we can use the results from the candidates CTE (if the union part is removed) since a failed match will eliminate
-- the row that is not part of the SELECT section, but this is a kind of sanity check
SELECT
c.table_schema,
c.table_name,
c.column_name AS table_column_name,
c.view_schema,
c.view_name,
c.view_column_name
FROM view_columns AS vc, candidates AS c
WHERE
vc.view_oid = c.view_oid AND
vc.column_name = c.view_column_name
ORDER BY c.view_schema, c.view_name, c.table_name, c.view_column_name
@francescomarucci

This comment has been minimized.

Copy link

@francescomarucci francescomarucci commented May 5, 2018

dear Ruslantalpa,
thanks a lot for you work!

I want just notice that the last revision of your query is not working when in the view definition is present some columns alias (like ... column_table_name AS column_view_name ...) while the first version (27 Feb 2016) does...

I'm not strong with regex, but I think that:

"CONCAT('SELECT.?((',vcu.table_name,')|(\w+)).(', vcu.column_name, ')(\sAS\s(")?([^"]+)\6)?.?FROM.*?',vcu.table_schema,'.(\2|',vcu.table_name,'\s+(AS\s)?\3)')"

it is not prepared for understands the column aliases...

some help would appreciated, even I actually solved with the first version of 27 Feb 2016...

thanks a lot,
francesco

@steve-chavez

This comment has been minimized.

Copy link

@steve-chavez steve-chavez commented Jul 16, 2018

@francescomarucci This query is going to be replaced, see the new query in PostgREST/postgrest#945 (comment), maybe you can run it and share your results.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment