On Sun, 5 Mar 2006 sramsay@uga.edu wrote:
> On Sun, Mar 05, 2006 at 11:38:46AM -0800, Stephan Szabo wrote:
> > > But imagine instead that this function is more generic. You know
> > > that you're trying to get something that's equal to x and equal to
> > > y, but you don't know (until the function is called) what those
> > > rvalues should be. In other words, it's the 42 and the 47 that you
> > > don't know until runtime -- you always know what columns your
> > > searching on.
> >
> > Then you need to pass those in as well as something representing the row
> > that's being tested -- where clauses are filters on rows. The whole row
> > representation might be better than columns for some cases.
> >
> > For example:
> >
> > create table tt1(a int, b int);
> > create function f1(tt1, int) returns bool as 'select $1.a = $2' language
> > 'sql';
> >
> > select * from tt1 where f1(tt1, 1);
>
> Eureka! That does it:
>
> CREATE OR REPLACE FUNCTION xpath(event, lquery) RETURNS bool AS $$
> SELECT $1.ltree ~ $2;
> $$ LANGUAGE SQL;
>
> And it's fast (the event table is a view)!
>
> Interestingly, this version is very slow:
>
> CREATE OR REPLACE FUNCTION xpath(event, lquery) RETURNS bool AS
> $$
> BEGIN
> RETURN $1.ltree ~ $2;
> END
> $$ LANGUAGE plpgsql STABLE;
>
> EXPLAIN shows lots of sequential scans when you try to do it this
> way. No inlining, perhaps?
Yeah, I'd assume it's running the function once for each row after the
construction of the event row (so it's probably doing any joins in the
view first), whereas the SQL one might inline and be optimized differently
which might shrink the number of rows that any joins are being done on.
> I guess I am still a little confused as to why you have to pass the
> table in as a parameter -- why you can't just do:
>
> RETURN event.ltree ~ $2;
Well, the problem here is say you have a function f(int) that says RETURN
tt1.a = $1 like my example. What does "select * from tt2 where f(1)" or
"select * from tt1 a, tt1 b where f(1)" mean? Functions don't really have
much context sensitivity, and it's not always reasonable to expect to be
able to open the box to know things like f(x) in the above depends on
there being a single tt1 entry in the from clause (and trying to deal
with outer references would be an utter mess).