From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: sramsay@uga.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: functions in WHERE clause
Date: Sun, 5 Mar 2006 12:23:42 -0800 (PST)
Message-ID: <20060305121529.J65444@megazone.bigpanda.com> (raw)
In-Reply-To: <20060305201043.GA14773@cantor.english.uga.edu>
References: <20060305151652.GA14216@cantor.english.uga.edu>
<20060305172635.GA77911@winnie.fuhr.org>
<20060305174827.GA14433@cantor.english.uga.edu>
<10317.1141582600@sss.pgh.pa.us>
<20060305184903.GA11613@lachesis.english.uga.edu>
<20060305113419.P64418@megazone.bigpanda.com>
<20060305201043.GA14773@cantor.english.uga.edu>
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, sramsay@uga.edu
Subject: Re: functions in WHERE clause
In-Reply-To: <20060305121529.J65444@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox