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

From Chris Campbell
Subject Re: Passing arguments to views
Date
Msg-id 82B19ECD-CD74-4B78-85B0-C41678309004@bignerdranch.com
Whole thread Raw
In response to Re: Passing arguments to views  (Martijn van Oosterhout <kleptog@svana.org>)
Responses Re: Passing arguments to views
List pgsql-hackers
On Feb 3, 2006, at 08:50, Martijn van Oosterhout wrote:

> Hmm, we actually do inline SQL functions under certain situations, but
> only for "simple" queries (see inline_function in
> optimizer/util/clauses.c). One approach would be to expand that
> function to inline more complicated things.

>> * Better query execution performance because the planner can plan the
>> whole query (rewriting the original query to replace references to
>> the view with the view's definition -- this is how views work today)
>
> Well, the inlining would acheive the same effect.

So you think approaching it from the "beefing up functions" side  
would be better than the "beefing up views" side?

>> * PostgreSQL-tracked dependancies: views create dependencies on the
>> relations they reference -- functions do not
>
> Technically a bug. We should be tracking dependancies for functions
> anyway.

Well, it's impossible to track dependencies for all functions, since  
they're just strings (or compiled code in shared libraries) until  
they're executed. But maybe SQL language functions could be special- 
cased? Do you think it would be easier to add dependancy-tracking for  
functions, or would it be easier to implement this functionality  
using the more-restrictive-language but better-dependency-tracking  
view system? When you add dependencies for things that didn't have  
dependencies before (like non-SQL functions), you create all sorts of  
backwards-compatibility problems due to the ordering that things need  
to be dumped and created, and circular dependancies.

For example, this works:
    CREATE FUNCTION foo(INTEGER) RETURNS INTEGER AS 'BEGIN RETURN bar 
($1-1); END;' LANGUAGE plpgsql;
    CREATE FUNCTION bar(INTEGER) RETURNS INTEGER AS 'BEGIN IF $1 < 0  
THEN RETURN $1; ELSE RETURN foo($1); END IF; END;' LANGUAGE plpgsql;

But it wouldn't work if PostgreSQL tracked and enforced dependancies.  
But it could probably be done with SQL-language functions only. I  
don't know if we'd want to add dependancy tracking for functions if  
it only works for SQL-language functions, though.

> This is a good point. Though with syntactic sugar you could work  
> around
> this too...

Basically, how views do it? :) By auto-creating a table with the  
proper columns (for a function, that would be an auto-created type).

I'm looking for a function/view hybrid, taking features from each. It  
seems to me that views have most of the features I want (only missing  
the ability to pass arguments), so it's a shorter distance to the  
goal than by starting with functions.

Thanks!

- Chris



pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [PATCHES] Fix for running from admin account on win32
Next
From: Alvaro Herrera
Date:
Subject: Re: Need Help In arrays