table_view_column_relations.sql

---- 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

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

@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.