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

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

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
You can’t perform that action at this time.