Skip to content

Instantly share code, notes, and snippets.

@steve-chavez

steve-chavez/allSourceColumns.sql Secret

Last active December 10, 2020 11:53
Show Gist options
  • Save steve-chavez/7ee0e6590cddafb532e5f00c46275569 to your computer and use it in GitHub Desktop.
Save steve-chavez/7ee0e6590cddafb532e5f00c46275569 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
allSourceColumns.sql
-- An explanation for the PostgREST query that returns the table columns and aliases that a view uses for it's root select.
-- i.e. The query obtains the source table columns of a view.
-- It does this by parsing the pg_rewrite.ev_action column.
--
-- Note: Tested on pg 9.6, there's a slight change for pg 10
--
-- This is used in PostgREST to allow joins between views, we needed this query because
-- information_schema.view_column_usage doesn't provide the column aliases and it doesn't specify which columns are used at the root select.
--
-- Author: Steve Chávez https://github.com/steve-chavez
-- License: MIT
-- For explaining this, we'll use the same schema PostgREST uses for it's tests https://github.com/PostgREST/postgrest/blob/master/test/fixtures/schema.sql
-- First let's see the query tree structure(manually formatted) of some views and find a pattern(focus on the last :targetList property)
CREATE VIEW projects_view AS
SELECT projects.id,
projects.name,
projects.client_id
FROM projects;
select ev_action from pg_rewrite where ev_class = 'projects_view'::regclass::oid;
({QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks false
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable (
{RTE
:alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("id" "name" "client_id")} :rtekind 0
:relid 564854 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b)
:insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id" "name" "client_id")} :rtekind 0
:relid 564854 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b)
:insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias <> :eref {ALIAS :aliasname projects :colnames ("id" "name" "client_id")} :rtekind 0 :relid 564848 :relkind r
:tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 10 11)
:insertedCols (b) :updatedCols (b) :securityQuals <>})
:jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>}
:targetList (
{TARGETENTRY
:expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 37}
:resno 1 :resname id :ressortgroupref 0 :resorigtbl 564848 :resorigcol 1 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location 54}
:resno 2 :resname name :ressortgroupref 0 :resorigtbl 564848 :resorigcol 2 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 3 :varattno 3 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 3 :location 73}
:resno 3 :resname client_id :ressortgroupref 0 :resorigtbl 564848 :resorigcol 3 :resjunk false})
:onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <>
:limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>})
CREATE OR REPLACE VIEW projects_view_2 AS
SELECT projects.id AS "pId",
projects.name AS "pName",
projects.client_id,
s.name as client_name
FROM projects
LEFT JOIN LATERAL (
SELECT id, name FROM clients
) s ON projects.client_id = s.id;
select ev_action from pg_rewrite where ev_class = 'projects_view_2'::regclass::oid;
({QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks false
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable (
{RTE
:alias {ALIAS :aliasname old :colnames <>}
:eref {ALIAS :aliasname old :colnames ("pId" "pName" "client_id" "client_name")}
:rtekind 0 :relid 559367 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b)
:insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname new :colnames <>}
:eref {ALIAS :aliasname new :colnames ("pId" "pName" "client_id" "client_name")}
:rtekind 0 :relid 559367 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b)
:insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias <>
:eref {ALIAS :aliasname projects :colnames ("id" "name" "client_id")}
:rtekind 0 :relid 547438 :relkind r :tablesample <> :lateral false
:inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 10 11) :insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname s :colnames <>}
:eref {ALIAS :aliasname s :colnames ("id" "name")} :rtekind 1
:subquery {QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false
:hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable ({RTE
:alias <>
:eref {ALIAS :aliasname clients :colnames ("id" "name")}
:rtekind 0 :relid 547350 :relkind r :tablesample <> :lateral false
:inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 10) :insertedCols (b) :updatedCols (b) :securityQuals <>})
:jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 1}) :quals <>}
:targetList (
{TARGETENTRY
:expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 197}
:resno 1 :resname id :ressortgroupref 0 :resorigtbl 547350
:resorigcol 1 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 1 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 1 :varoattno 2 :location 201}
:resno 2 :resname name :ressortgroupref 0 :resorigtbl 547350 :resorigcol 2 :resjunk false})
:onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <> }
:security_barrier false :lateral true :inh false :inFromCl true :requiredPerms 0 :checkAsUser 0 :selectedCols (b)
:insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias <> :eref {ALIAS :aliasname unnamed_join :colnames ("id" "name" "client_id" "id" "name")} :rtekind 2 :jointype 1
:joinaliasvars (
{VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location -1}
{VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location -1}
{VAR :varno 3 :varattno 3 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 3 :location -1}
{VAR :varno 4 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 4 :varoattno 1 :location -1}
{VAR :varno 4 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 4 :varoattno 2 :location -1})
:lateral false :inh false :inFromCl true :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>}
)
:jointree {FROMEXPR :fromlist ({JOINEXPR :jointype 1 :isNatural false :larg {RANGETBLREF :rtindex 3} :rarg {RANGETBLREF :rtindex 4}
:usingClause <> :quals {
OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args (
{VAR :varno 3 :varattno 3 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 3 :location 226}
{VAR :varno 4 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 4 :varoattno 1 :location 247}) :location 245}
:alias <> :rtindex 5}) :quals <>
}
:targetList (
{TARGETENTRY
:expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 50}
:resno 1 :resname pId :ressortgroupref 0 :resorigtbl 547438 :resorigcol 1 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location 76}
:resno 2 :resname pName :ressortgroupref 0 :resorigtbl 547438 :resorigcol 2 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 3 :varattno 3 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 3 :location 106}
:resno 3 :resname client_id :ressortgroupref 0 :resorigtbl 547438 :resorigcol 3 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 4 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 4 :varoattno 2 :location 130}
:resno 4 :resname client_name :ressortgroupref 0 :resorigtbl 547350 :resorigcol 2 :resjunk false})
:onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>
})
create view person_detail as
select p.id, p.name, s.count as sent, r.count as received
from person p
join lateral (select message.sender, count(message.id) as count from message group by message.sender) s on s.sender = p.id
join lateral (select message.recipient, count(message.id) as count from message group by message.recipient) r on r.recipient = p.id;
select ev_action from pg_rewrite where ev_class = 'person_detail'::regclass::oid;
({QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks false
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable (
{RTE
:alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("id" "name" "sent" "received")} :rtekind 0 :relid 565329
:relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b)
:updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id" "name" "sent" "received")} :rtekind 0 :relid 565329
:relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b)
:updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname p :colnames <>} :eref {ALIAS :aliasname p :colnames ("id" "name")} :rtekind 0 :relid 565302 :relkind r :tablesample <>
:lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 10) :insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname s :colnames <>} :eref {ALIAS :aliasname s :colnames ("sender" "count")} :rtekind 1
:subquery {QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs true :hasWindowFuncs false :hasSubLinks false
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable (
{RTE
:alias <> :eref {ALIAS :aliasname message :colnames ("id" "body" "sender" "recipient")} :rtekind 0 :relid 565310 :relkind r :tablesample <>
:lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 11) :insertedCols (b) :updatedCols (b) :securityQuals <>})
:jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 1}) :quals <>}
:targetList (
{TARGETENTRY
:expr {VAR :varno 1 :varattno 3 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 3 :location 128}
:resno 1 :resname sender :ressortgroupref 1 :resorigtbl 565310 :resorigcol 3 :resjunk false}
{TARGETENTRY
:expr {AGGREF :aggfnoid 2147 :aggtype 20 :aggcollid 0 :inputcollid 0 :aggtranstype 0 :aggargtypes (o 23) :aggdirectargs <> :args ({TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 150} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :aggorder <> :aggdistinct <> :aggfilter <> :aggstar false :aggvariadic false :aggkind n :agglevelsup 0 :aggsplit 0 :location 144}
:resno 2 :resname count :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false})
:onConflict <> :returningList <> :groupClause ({SORTGROUPCLAUSE :tleSortGroupRef 1 :eqop 410 :sortop 412 :nulls_first false :hashable true})
:groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setOperations <>
:constraintDeps <>}
:security_barrier false :lateral true :inh false :inFromCl true :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b)
:securityQuals <>}
{RTE
:alias <> :eref {ALIAS :aliasname unnamed_join :colnames ("id" "name" "sender" "count")} :rtekind 2 :jointype 0
:joinaliasvars (
{VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location -1}
{VAR :varno 3 :varattno 2 :vartype 1043 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location -1}
{VAR :varno 4 :varattno 1 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 4 :varoattno 1 :location -1}
{VAR :varno 4 :varattno 2 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 4 :varoattno 2 :location -1})
:lateral false :inh false :inFromCl true :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias {ALIAS :aliasname r :colnames <>} :eref {ALIAS :aliasname r :colnames ("recipient" "count")} :rtekind 1
:subquery {QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs true :hasWindowFuncs false :hasSubLinks false
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable (
{RTE
:alias <> :eref {ALIAS :aliasname message :colnames ("id" "body" "sender" "recipient")} :rtekind 0 :relid 565310 :relkind r :tablesample <>
:lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 12) :insertedCols (b) :updatedCols (b)
:securityQuals <>})
:jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 1}) :quals <>}
:targetList (
{TARGETENTRY
:expr {VAR :varno 1 :varattno 4 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 4 :location 253}
:resno 1 :resname recipient :ressortgroupref 1 :resorigtbl 565310 :resorigcol 4 :resjunk false}
{TARGETENTRY
:expr {AGGREF :aggfnoid 2147 :aggtype 20 :aggcollid 0 :inputcollid 0 :aggtranstype 0 :aggargtypes (o 23) :aggdirectargs <> :args ({TARGETENTRY :expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 278} :resno 1 :resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :aggorder <> :aggdistinct <> :aggfilter <> :aggstar false :aggvariadic false :aggkind n :agglevelsup 0 :aggsplit 0 :location 272}
:resno 2 :resname count :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false})
:onConflict <> :returningList <> :groupClause ({SORTGROUPCLAUSE :tleSortGroupRef 1 :eqop 410 :sortop 412 :nulls_first false :hashable true})
:groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setOperations <>
:constraintDeps <>} :security_barrier false :lateral true :inh false :inFromCl true :requiredPerms 0 :checkAsUser 0 :selectedCols (b)
:insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE
:alias <> :eref {ALIAS :aliasname unnamed_join :colnames ("id" "name" "sender" "count" "recipient" "count")} :rtekind 2 :jointype 0
:joinaliasvars (
{VAR :varno 5 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 5 :varoattno 1 :location -1}
{VAR :varno 5 :varattno 2 :vartype 1043 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 5 :varoattno 2 :location -1}
{VAR :varno 5 :varattno 3 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 5 :varoattno 3 :location -1}
{VAR :varno 5 :varattno 4 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 5 :varoattno 4 :location -1}
{VAR :varno 6 :varattno 1 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 6 :varoattno 1 :location -1}
{VAR :varno 6 :varattno 2 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 6 :varoattno 2 :location -1})
:lateral false :inh false :inFromCl true :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>})
:jointree {FROMEXPR :fromlist ({JOINEXPR :jointype 0 :isNatural false :larg {JOINEXPR :jointype 0 :isNatural false :larg {RANGETBLREF :rtindex 3} :rarg {RANGETBLREF :rtindex 4} :usingClause <> :quals {OPEXPR :opno 416 :opfuncid 474 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 4 :varattno 1 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 4 :varoattno 1 :location 214} {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 225}) :location 223} :alias <> :rtindex 5} :rarg {RANGETBLREF :rtindex 6} :usingClause <> :quals {OPEXPR :opno 416 :opfuncid 474 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 6 :varattno 1 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 6 :varoattno 1 :location 345} {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 359}) :location 357} :alias <> :rtindex 7}) :quals <>}
:targetList (
{TARGETENTRY
:expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 38}
:resno 1 :resname id :ressortgroupref 0 :resorigtbl 565302 :resorigcol 1 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 3 :varattno 2 :vartype 1043 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location 44}
:resno 2 :resname name :ressortgroupref 0 :resorigtbl 565302 :resorigcol 2 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 4 :varattno 2 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 4 :varoattno 2 :location 52}
:resno 3 :resname sent :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 6 :varattno 2 :vartype 20 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 6 :varoattno 2 :location 69}
:resno 4 :resname received :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false})
:onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>})
-- Seems we're lucky, despite the different structure of the views the root :targetList(the last one) maintains the names of the view columns
-- with the aliases(it even maintains capital letters) in the :resname property, the oid of the table were the column belongs in :resorigtbl and
-- the :resorigcol contains the position of the column in the table.
-- The name of the table can be obtained by joining with the pg_class table and the name of the column by joining with the pg_attribute table.
--
-- So we just need to split the ev_action text by targetList and pick the last one, remove the tail(starts from :onConflict), split by TARGETENTRY,
-- and obtain the resname, resorigtbl resorigcol. An example with the `person_detail` view:
with
view as(
select
ev_action as view_definition
from pg_rewrite
where ev_class = 'person_detail'::regclass::oid
),
target_lists as(
select
regexp_split_to_array(view_definition, 'targetList') as x
from view
),
last_target_list_wo_tail as(
select
(regexp_split_to_array(x[array_upper(x, 1)], ':onConflict'))[1] as x
from target_lists
),
target_entries as(
select
unnest((regexp_split_to_array(x, 'TARGETENTRY'))[2:]) as entry -- first split has a "({", just ignore it
from last_target_list_wo_tail
)
select
substring(entry from ':resname (.*?) :') as view_colum_name,
substring(entry from ':resorigtbl (.*?) :') as resorigtbl,
substring(entry from ':resorigcol (.*?) :') as resorigcol
from target_entries;
┌─────────────────┬────────────┬────────────┐
│ view_colum_name │ resorigtbl │ resorigcol │
├─────────────────┼────────────┼────────────┤
│ id │ 565302 │ 1 │
│ name │ 565302 │ 2 │
│ sent │ 0 │ 0 │
│ received │ 0 │ 0 │
└─────────────────┴────────────┴────────────┘
-- Seems to work fine, notice that there are some 0's that indicate the view column doesn't belong to any table.
-- These columns have gone through some process, this is also good for us since we only care about unprocessed table columns.
-- We can filter those 0's later.
--
-- Now, we'll try the previous query in a view with subselects:
create view test.authors_books_number as
select
id, name,
(select count(*) from books where author_id = authors.id) as books_authored
from authors;
with
view as(
select
ev_action as view_definition
from pg_rewrite
where ev_class = 'authors_books_number'::regclass::oid
),
target_lists as(
select
regexp_split_to_array(view_definition, 'targetList') as x
from view
),
last_target_list_wo_tail as(
select
(regexp_split_to_array(x[array_upper(x, 1)], ':onConflict'))[1] as x
from target_lists
),
target_entries as(
select
unnest((regexp_split_to_array(x, 'TARGETENTRY'))[2:]) as entry -- first split has a "({", we'll just ignore it
from last_target_list_wo_tail
)
select
substring(entry from ':resname (.*?) :') as view_colum_name,
substring(entry from ':resorigtbl (.*?) :') as resorigtbl,
substring(entry from ':resorigcol (.*?) :') as resorigcol
from target_entries;
┌─────────────────┬────────────┬────────────┐
│ view_colum_name │ resorigtbl │ resorigcol │
├─────────────────┼────────────┼────────────┤
│ count │ 0 │ 0 │
└─────────────────┴────────────┴────────────┘
-- No luck this time, it's giving us the "count" column used in the subselect, let's see it's full definition and
-- find the problem(focus on the :subselect part).
select ev_action from pg_rewrite where ev_class = 'authors_books_number'::regclass::oid;
({QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks true
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable (
{RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("id" "name" "books_authored")} :rtekind 0 :relid 571893 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id" "name" "books_authored")} :rtekind 0 :relid 571893 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>}
{RTE :alias <> :eref {ALIAS :aliasname authors :colnames ("id" "name")} :rtekind 0 :relid 565282 :relkind v :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 10) :insertedCols (b) :updatedCols (b) :securityQuals <>})
:jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>}
:targetList (
{TARGETENTRY
:expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 51}
:resno 1 :resname id :ressortgroupref 0 :resorigtbl 565282 :resorigcol 1 :resjunk false}
{TARGETENTRY
:expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnoold 3 :varoattno 2 :location 55}
:resno 2 :resname name :ressortgroupref 0 :resorigtbl 565282 :resorigcol 2 :resjunk false}
{TARGETENTRY
:expr {SUBLINK :subLinkType 4 :subLinkId 0 :testexpr <> :operName <>
:subselect {QUERY
:commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs true :hasWindowFuncs false :hasSubLinks false
:hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <>
:rtable ({RTE :alias <> :eref {ALIAS :aliasname books :colnames ("id" "title" "publication_year" "author_id")} :rtekind 0 :relid 565286 :relkind v :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 12) :insertedCols (b) :updatedCols (b) :securityQuals <>})
:jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 4 :location 97} {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 1 :varnoold 3 :varoattno 1 :location 109}) :location 107}}
:targetList (
{TARGETENTRY
:expr {AGGREF :aggfnoid 2803 :aggtype 20 :aggcollid 0 :inputcollid 0 :aggtranstype 0 :aggargtypes <> :aggdirectargs <> :args <> :aggorder <> :aggdistinct <> :aggfilter <> :aggstar true :aggvariadic false :aggkind n :agglevelsup 0 :aggsplit 0 :location 71}
:resno 1 :resname count :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false})
:onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>}
:location 63}
:resno 3 :resname books_authored :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false})
:onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>})
-- Now, we can see the :subselect also has a similar structure to the root QUERY, and this :subselect get's added in the root :targetList
-- that we're after, our previous query it's obtaining the last :targetList so that's why we see "count" as our detected view column.
--
-- The good thing is that the root :targetList maintains the :resname, :resorigtbl, :resorigcol attributes unchanged,
-- and we're only interested in obtaining these.
--
-- At this point we could choose to not allow subselects in views and make PostgREST just not detect any column when the view has one.
-- But we'll try a best effort approach regarding the view detection.
--
-- So, the simplest thing we can do is to just delete this :subselect,
-- matching the text starting from ":subselect" and ending in ":constraintDeps <>} :location" (in pg10 it ends with ":stmt_len 0}").
--
-- Let's try a view with more subselects and see how this idea holds up.
create view test.authors_books_number_by_decade as
select
id,
(
select
count(*)
from forties_books where author_id = authors.id
) as books_authored_in_forties,
(
select
count(*)
from fifties_books where author_id = authors.id
) as books_authored_in_fifties,
(
select
count(*)
from sixties_books where author_id = authors.id
) as books_authored_in_sixties,
name
from authors;
with
view as(
select
ev_action as view_definition
from pg_rewrite
where ev_class = 'authors_books_number_by_decade'::regclass::oid
),
removed_subselects as(
select
regexp_replace(view_definition, ':subselect {.*?:constraintDeps <>} :location', '', 'g') as x -- g means global, we'll replace every ocurrence
from view
),
target_lists as(
select
regexp_split_to_array(x, 'targetList') as x
from removed_subselects
),
last_target_list_wo_tail as(
select
(regexp_split_to_array(x[array_upper(x, 1)], ':onConflict'))[1] as x
from target_lists
),
target_entries as(
select
unnest((regexp_split_to_array(x, 'TARGETENTRY'))[2:]) as entry
from last_target_list_wo_tail
)
select
substring(entry from ':resname (.*?) :') as view_colum_name,
substring(entry from ':resorigtbl (.*?) :') as resorigtbl,
substring(entry from ':resorigcol (.*?) :') as resorigcol
from target_entries;
┌───────────────────────────┬────────────┬────────────┐
│ view_colum_name │ resorigtbl │ resorigcol │
├───────────────────────────┼────────────┼────────────┤
│ id │ 565282 │ 1 │
│ books_authored_in_forties │ 0 │ 0 │
│ books_authored_in_fifties │ 0 │ 0 │
│ books_authored_in_sixties │ 0 │ 0 │
│ name │ 565282 │ 2 │
└───────────────────────────┴────────────┴────────────┘
-- Seems the problem is gone.
--
-- Let's try a more nested view now.
create view test.authors_books_number_more_nested as
select
id, name,
(
select
count(*)
from forties_books where author_id = x.id
) as books_authored_in_forties,
(
select
count(*)
from (
select id
from fifties_books where author_id = x.id
union
select id
from sixties_books where author_id = x.id
) _
) as books_authored_in_fifties_and_sixties,
books_authored
from (
select
id, name, books_authored
from (
select
id, name,
(select count(*) from books where author_id = authors.id) as books_authored
from authors
) _
) x;
with
view as(
select
ev_action as view_definition
from pg_rewrite
where ev_class = 'authors_books_number_more_nested'::regclass::oid
),
removed_subselects as(
select
regexp_replace(view_definition, ':subselect {.*?:constraintDeps <>} :location', '', 'g') as x
from view
),
target_lists as(
select
regexp_split_to_array(x, 'targetList') as x
from removed_subselects
),
last_target_list_wo_tail as(
select
(regexp_split_to_array(x[array_upper(x, 1)], ':onConflict'))[1] as x
from target_lists
),
target_entries as(
select
unnest((regexp_split_to_array(x, 'TARGETENTRY'))[2:]) as entry
from last_target_list_wo_tail
)
select
substring(entry from ':resname (.*?) :') as view_colum_name,
substring(entry from ':resorigtbl (.*?) :') as resorigtbl,
substring(entry from ':resorigcol (.*?) :') as resorigcol
from target_entries;
┌───────────────────────────────────────┬────────────┬────────────┐
│ view_colum_name │ resorigtbl │ resorigcol │
├───────────────────────────────────────┼────────────┼────────────┤
│ id │ 565282 │ 1 │
│ name │ 565282 │ 2 │
│ books_authored_in_forties │ 0 │ 0 │
│ books_authored_in_fifties_and_sixties │ 0 │ 0 │
│ books_authored │ 0 │ 0 │
└───────────────────────────────────────┴────────────┴────────────┘
-- Working fine, now the only thing that's left is the join with the pg_class and pg_attribute tables, this is largely mechanical so it's omitted here,
-- see the PostgREST allSynoyms query https://github.com/PostgREST/postgrest/blob/master/src/PostgREST/DbStructure.hs#L697
-- to see how this is done.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment