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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
