Re: functions in WHERE clause

On Sun, Mar 05, 2006 at 10:26:35AM -0700, Michael Fuhr wrote:
> On Sun, Mar 05, 2006 at 10:16:52AM -0500, sramsay(at)uga(dot)edu wrote:
> > I've got one of these:
> >
> > SELECT * from some_table WHERE
> > test_for_equality_is_syntactically_ugly;
> >
> > What I'd like to do is encapsulate the WHERE clause in a function,
> > but I'm having no end of trouble.
>
> Would a view work? If not then please provide a more concrete
> example that shows what you're trying to do.
>
> CREATE VIEW foo AS
> SELECT * FROM some_table
> WHERE test_for_equality_is_syntactically_ugly;

Thanks for reply!

No, because test_for_equality_is_syntactically_ugly varies.

Here's the specifics:

I've written an XML database (of sorts) that uses the ltree contrib
module to find elements that belong to particular nodes. Right now,
if you wanted to find all the word tokens found within the
titleStmt element of a set of XML documents, you'd do something like this:

SELECT token FROM event WHERE ltree ~ '*.titleStmt.*';

(ltree contains a set of "paths" that together express the
hierarchical relationships in the documents, so you can format that
bit after the ~ to include any element, and you can also use other
kinds of operators to get ancestors, children, etc.).

Now, I realize this isn't *really* all that syntactically ugly, but
it would be really nice if the user of the db could type in an XPath
like so:

SELECT token FROM event WHERE xpath("//titleStmt");

This will require some munging inside a function to go from the
XPath to the ltree-style regex expression, but I'm finding that I'm
having more basic troubles.

You can't do this:

CREATE FUNCTION xpath(lquery) RETURNS ltree AS $$
SELECT ltree FROM event WHERE ltree ~ $1;
$$ LANGUAGE SQL;

Because that only returns the first value from the SELECT (and it's
not pl-pgsql anyway).

But I also can't get this kind of thing to work:

CREATE FUNCTION xpath(lquery) RETURNS SETOF ltree AS $$
DECLARE
tree record;
BEGIN
FOR tree IN SELECT ltree FROM event WHERE ltree ~ $1 LOOP
RETURN NEXT tree;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;

Because SETOF won't work in a WHERE context.

Part of the problem is that I don't really understand how WHERE
constraints work under the hood. The lvalue in the WHERE clause
expects a boolean, but I think that really means "evaluate the truth
or falsehood of this expression on all rows in the table specified
in the FROM clause." So how do you get a function to play nicely
with that concept? "The argument of WHERE must not return a set,"
as the psql shell keeps telling me, but is there anything it would
accept that would make this work?

Thanks again,

Steve

--
Stephen Ramsay
Assistant Professor
Department of English
University of Georgia
email: sramsay(at)uga(dot)edu
web: http://cantor.english.uga.edu/
PGP Public Key ID: 0xA38D7B11