Explaining View Embedding in PostgREST #1: Rationale and background

pfkSourceColumns.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.
--
-- 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.
--
-- This first part explains the rationale behind this.
-- The second part at https://gist.github.com/wolfgangwalther/3a8939da680c24ad767e93ad2c183089 explains in detail how the pg_node_tree is parsed by transformation to JSON
--
-- 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 oid of the table were the column belongs in :resorigtbl and 
-- * the :resorigcol contains the position of the column in the table.
-- The names of the table and column can be obtained from the system catalogs.

-- The :targetList used to be parsed by regex operations directly. This has some limitations regarding sub-queries and set operations.
-- Now the pg_node_tree is transformed to JSON, of which the :targetList can easily be extracted.
-- See the link in the header for an explanation of that transformation.