Skip site navigation (1) Skip section navigation (2)

Re: Passing arguments to views

From: Chris Campbell <chris(at)bignerdranch(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: Tino Wildenhain <tino(at)wildenhain(dot)de>, Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Passing arguments to views
Date: 2006-02-03 14:18:51
Message-ID: 82B19ECD-CD74-4B78-85B0-C41678309004@bignerdranch.com (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2006-02-03 14:47:49
Subject: Re: Need Help In arrays
Previous:From: Bruce MomjianDate: 2006-02-03 13:58:30
Subject: Re: [PATCHES] Fix for running from admin account on win32

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group