From: Tom Lane <tgl@sss.pgh.pa.us>
To: sramsay@uga.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: functions in WHERE clause
Date: Sun, 05 Mar 2006 13:16:40 -0500
Message-ID: <10317.1141582600@sss.pgh.pa.us> (raw)
In-Reply-To: <20060305174827.GA14433@cantor.english.uga.edu>
References: <20060305151652.GA14216@cantor.english.uga.edu>
<20060305172635.GA77911@winnie.fuhr.org>
<20060305174827.GA14433@cantor.english.uga.edu>
sramsay@uga.edu writes:
> You can't do this:
> CREATE FUNCTION xpath(lquery) RETURNS ltree AS $$
> SELECT ltree FROM event WHERE ltree ~ $1;
> $$ LANGUAGE SQL;
That would work fine if you said RETURNS SETOF ltree.
> 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;
That should work too, except that you are trying to return a record
not an ltree value. Try "RETURN NEXT tree.ltree".
> Because SETOF won't work in a WHERE context.
Possibly you need to read the error messages you are getting more
closely, because I'm pretty sure whatever it said had nothing to
do with either SETOF or WHERE ...
regards, tom lane
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: tgl@sss.pgh.pa.us, sramsay@uga.edu
Subject: Re: functions in WHERE clause
In-Reply-To: <10317.1141582600@sss.pgh.pa.us>
* 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