I have been looking through FAQs and the docs and I cannot seem to find the
answer to this question. If someone can point me to documentation I would
really appreciate it.

I am trying to run this function but the return is not correct. If I run the
select statement from the psql command line it works. My guess is that the
WHERE clause could be causing the problem. Then again, it may be how I am
using the FOR loop. The ides column is of type TEXT.

CREATE OR REPLACE FUNCTION sp_description_search(varchar) RETURNS varchar AS
$$
DECLARE
myrec record;
BEGIN
FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Thanks so much for any insight you can give me!!!
Mike

Mike Plemmons wrote:
> I am trying to run this function but the return is not correct.

PS - next time, details of what it *did* return and why that wasn't
correct would be useful. Not needed in this case, but good practice anyway.

PPS - the subject line was pretty good, but better might have been
"Problem with LIKE and variables in a plpgsql function"

PPPS - This is a fairly inefficient way of running simple queries.

PPPPS - Thanks for the question!

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Mike Plemmons wrote:
> I am trying to run this function but the return is not correct. If I run the
> select statement from the psql command line it works. My guess is that the
> WHERE clause could be causing the problem. Then again, it may be how I am
> using the FOR loop. The ides column is of type TEXT.

> FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP

You're assuming plpgsql does variable interpolation in a similar way to
Perl/shell etc. It doesn't. You'll want something like:
...LIKE '%' || $1 || '%'

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Mike Plemmons wrote:
>>
>>These are great tips! I will be more specific the next time I post.
>>Regarding the ineffiencient query. What do you suggest as a better way? I
>>would rather learn proper methods than continue to use improper ones.

Just send the query directly from the application - the way you're doing
it, the function will have to assemble the query, execute it, fetch all
the results and then return them to your application. Of course, if your
query altered each row as it fetched them (e.g. to calculate a running
total) then this would be a sensible approach.

PS - don't forget to CC: the list when replying - that's the convention
around here.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org