Re: Passing arguments to views - Mailing list pgsql-hackers

On Feb 3, 2006, at 10:25, Martijn van Oosterhout wrote:

> Sure, for most procedural languages you can't do much. But we do do
> syntax checking already and checking that the necessary functions  
> exist
> can be considered part of that. It' s not terribly important though.

Dependancy tracking needs to be more than a "best effort." If you  
can't do it accurately and completely, then I don't think it's worth  
doing at all.

But I agree with you that syntax checking is probably sufficient. We  
don't need true dependancy tracking for functions.

The only failure case (where syntax checking won't help you) is  
deleting (or renaming, or modifying) a table that a function was  
using. If you were to run or re-define the function, you'd learn  
about the missing (or renamed, or modified) table, whereas the  
dependancy-tracking system would prevent you from making the changes  
to the referenced table in the first place.

> Ok, here's the deal. A view is nothing more than a RULE. Creating a
> view does this automatically

Technically, it's a table and a rule, both of which are created by  
the CREATE VIEW command. We were talking about syntactic sugar, and  
CREATE VIEW is syntactic sugar for doing a CREATE TABLE and a CREATE  
RULE. That was my comparison. I'm aware of how views work. Here's the  
deal: I want to beef up rules versus beefing up functions. Maybe  
that's not the way to go; I'm enjoying this discussion and your  
insights.

> CREATE RULE blah AS ON SELECT TO myview DO INSTEAD <select statement>;
>
> Now, say you wanted to add parameters to this, would you restrict  
> it to
> SELECT rules, what about UPDATE or DELETE rules?

I don't see a huge use case for anything but SELECT rules, but I  
think it could be extended to any rule type. Maybe the CREATE RULE  
syntax could be something like:
    CREATE RULE blah AS ON SELECT(INTEGER, INTEGER, DATE) TO myview  
DO INSTEAD SELECT * FROM sale WHERE sale_date = $3;

> The other issue is that currently you can tell from looking at a
> statement whether something is a function or a table (is it  
> followed by
> an open parenthesis?). With the above change you can't anymore, which
> might mean you can't have functions and tables with the same names
> because they'd be ambiguous.

Right. I said that my example syntax was deficient in this regard in  
the first message in this thread. And I solicited ideas for a better  
(unambiguous) syntax. I'm sure we would be able to come up with  
something. Maybe using square brackets instead of parentheses? Curly  
braces? "myview->(1, 2, 3)" notation? Since views are tables (when  
parsing the query, at least) we'd have to allow this syntax for any  
table reference, but throw an error (or silently discard the  
arguments) if the table didn't have a rule matching the argument types?

> On the whole, I think allowing the server to inline SRFs would be a  
> far
> better way to go...

Maybe, but the highly-structured view definition syntax and  
everything that comes with it (dependancy tracking primarily) is so  
tasty. I think a little grammar hacking and a couple extra columns in  
pg_rewrite (nargs and argtypes) would get us most of the way there.

I would much rather put more stringent requirements on the programmer  
when defining his query (like a view), versus letting him submit any  
old string as a function (like a function). The database can do so  
much more when it's able to work with a better representation of the  
computation.

At the core, I want query rewriting with arguments. That sounds like  
a better fit for views/rules than functions, so that's why I keep  
coming back to it.

Thanks!

- Chris