Mapping view columns to their source columns
Hi,
I'm trying to map view columns to their source columns using the system
catalogs and information schema, but not having much luck. It's easy to
determine which columns a view *depends *on, but not how those columns are
mapped to the columns of the view. It seems like the only way to do is this
is to somehow query the pg_node_tree in pg_rewrite.ev_call.
I'm trying to do this to provide meta data for an API. For example:
- a database design's API is implemented via functions
- a function returns a setof a particular view
- the web server that is serving the data might want to manipulate the
returned data based on the types/constraints of the underlying columns of
the view.
It seems like mapping view columns to their source columns should be
something that's been done before. Is it possible?
Matt Andrews
0400 990 131
Matt Andrews <mattandrews@massey.com.au> writes:
I'm trying to map view columns to their source columns using the system
catalogs and information schema, but not having much luck. It's easy to
determine which columns a view *depends *on, but not how those columns are
mapped to the columns of the view. It seems like the only way to do is this
is to somehow query the pg_node_tree in pg_rewrite.ev_call.
Yeah, that's what you'd have to do. The system doesn't track this
any more finely than "does the view as a whole depend on this column",
partly because we don't need to and partly because it's hard to define
reasonably. What do you want to do with, say,
select a, b+c from tab;
It gets even more interesting when you think about aggregates and
other advanced features:
select a, max(b) as mb from tab group by a;
Any reasonable semantic analysis would have to conclude that the
mb column depends on both a and b.
Once you had answers to these definitional questions, you could
think about analyzing the view's query tree to get the answers
you want. Unfortunately, doing that from client side is not
supported at all; if you try you'll find yourself maintaining
a lot of messy code that we *will* break on a regular basis.
It would be less hard in a backend extension, but I suspect
you don't want to go there :-(
regards, tom lane
After thinking about this one for a while, I imagined even more nightmarish
scenarios than what you've just described here, and mapping the source
columns no longer seems like a viable idea.
Fortunately, there are a few work arounds I can rely on that particular to
our database design, which means I will be able to use the system catalogs
in a round about way.
Incidentally, I have become interested in the pg_node_tree type. I can't
find much info on it. Would that be in the source? What would be the first
steps for writing an extension for this sort of thing?
Matt
0400 990 131
On Wed, Sep 25, 2019 at 2:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Matt Andrews <mattandrews@massey.com.au> writes:
I'm trying to map view columns to their source columns using the system
catalogs and information schema, but not having much luck. It's easy to
determine which columns a view *depends *on, but not how those columnsare
mapped to the columns of the view. It seems like the only way to do is
this
is to somehow query the pg_node_tree in pg_rewrite.ev_call.
Yeah, that's what you'd have to do. The system doesn't track this
any more finely than "does the view as a whole depend on this column",
partly because we don't need to and partly because it's hard to define
reasonably. What do you want to do with, say,select a, b+c from tab;
It gets even more interesting when you think about aggregates and
other advanced features:select a, max(b) as mb from tab group by a;
Any reasonable semantic analysis would have to conclude that the
mb column depends on both a and b.Once you had answers to these definitional questions, you could
think about analyzing the view's query tree to get the answers
you want. Unfortunately, doing that from client side is not
supported at all; if you try you'll find yourself maintaining
a lot of messy code that we *will* break on a regular basis.
It would be less hard in a backend extension, but I suspect
you don't want to go there :-(regards, tom lane
Matt Andrews <mattandrews@massey.com.au> writes:
Incidentally, I have become interested in the pg_node_tree type. I can't
find much info on it. Would that be in the source? What would be the first
steps for writing an extension for this sort of thing?
pg_node_tree is just a serialization of the tree-of-Node-structs
representation used inside the server. See
src/include/nodes/primnodes.h and adjacent files for Node definitions.
The actual serialization code is in src/backend/nodes/outfuncs.c
and adjacent files.
(Beware, however, that there's proposals floating around to change
the serialization format.)
regards, tom lane