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