Re: functions in WHERE clause
От | sramsay@uga.edu |
---|---|
Тема | Re: functions in WHERE clause |
Дата | |
Msg-id | 20060305174827.GA14433@cantor.english.uga.edu обсуждение исходный текст |
Ответ на | Re: functions in WHERE clause (Michael Fuhr <mike@fuhr.org>) |
Ответы |
Re: functions in WHERE clause
|
Список | pgsql-sql |
On Sun, Mar 05, 2006 at 10:26:35AM -0700, Michael Fuhr wrote: > On Sun, Mar 05, 2006 at 10:16:52AM -0500, sramsay@uga.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; BEGINFOR 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@uga.edu web: http://cantor.english.uga.edu/ PGP Public Key ID: 0xA38D7B11
В списке pgsql-sql по дате отправления: