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

Re: Passing arguments to views

From: Chris Campbell <chris(at)bignerdranch(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, 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 16:38:14
Message-ID: 732A15C8-03BE-4B91-8346-D4EA7D6101C2@bignerdranch.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Feb 3, 2006, at 11:21, Tom Lane wrote:

> The SRF concept captures what you want a whole lot better.  If the
> implementation isn't up to snuff, we should improve it, not warp other
> pieces of the system.

Point taken. The rewriting concept is what I'm after; if that can be  
done pre-planning with SQL functions, I'm all for it. I just thought  
that since rules already do rewriting, that's the best thing to start  
building on.

> Martijn mentioned the idea of inlining SQL functions that return sets
> --- this is something I've toyed with too, but not got round to  
> looking
> at seriously.  AFAICS it would accomplish everything that you could do
> with parameters in ON SELECT rules, considering the existing
> restrictions on what can be in an ON SELECT rule.  And it wouldn't
> require any new concepts at all, just a few(?) pages of code.

True, as long as there's a hook to do the inlining/rewriting before  
the query's planned. I guess we can see function calls at the parse  
stage, check to see if they're SQL functions or not, grab the prosrc,  
do the substitution, then re-parse?

I guess I can live without the dependancy tracking. I can always dump  
and reload my database to re-parse all the functions. Maybe we could  
have a RELOAD FUNCTION command that would just re-parse an existing  
function, so I don't have to dump and reload?

What about auto-creating a composite type for the function's return  
type based on the query definition? (Like how CREATE VIEW creates an  
appropriate table definition.) Do you see a way for CREATE FUNCTION  
to do that? The problem is that you have to specify a return type in  
CREATE FUNCTION.

Maybe an extension to CREATE FUNCTION as a shorthand for set- 
returning SQL functions? Like:

    CREATE SQL FUNCTION sales_figures(DATE) AS SELECT ... FROM ...  
WHERE sale_date <= $1;

It would (1) automatically create a composite type (newtype) for the  
return value, and (2) do a

    CREATE FUNCTION sales_figures(DATE) RETURNS SETOF newtype AS  
'...' LANGUAGE sql.

How much do I have to justify a patch for non-standard "RELOAD  
FUNCTION" and "CREATE SQL FUNCTION" commands (as described) in the  
grammar? :)

Thanks!

- Chris


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-02-03 17:27:26
Subject: Re: Passing arguments to views
Previous:From: Tom LaneDate: 2006-02-03 16:21:36
Subject: Re: Passing arguments to views

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