Thread: Question about functions

I have been looking through FAQs and the docs and I cannot seem to find the answer to this question.  If someone can
pointme to documentation I would really appreciate it.<br /><br /> I am trying to run this function but the return is
notcorrect.  If I run the select statement from the psql command line it works.  My guess is that the WHERE clause
couldbe causing the problem.  Then again, it may be how I am using the FOR loop.  The ides column is of type TEXT.<br
/><br/> CREATE OR REPLACE FUNCTION sp_description_search(varchar) RETURNS varchar AS $$<br /> DECLARE<br /> myrec
record;<br/> BEGIN<br /> FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP<br /> RETURN NEXT myrec;<br />
ENDLOOP;<br /> RETURN;<br /> END;<br /> $$ LANGUAGE 'plpgsql';<br /><br /> Thanks so much for any insight you can give
me!!!<br/> Mike<br /> 

Mike,

> 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 $$         -- <== Problem is here
> DECLARE
> myrec record;
> BEGIN
> FOR myrec IN SELECT * FROM tblStuff WHERE ides LIKE '%$1%' LOOP
> RETURN NEXT myrec;            -- <== Problem is here
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE 'plpgsql';

You need to declare this as a set-returning function if you're using
RETURN NEXT.

Best regards,

Ben Stewart

--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/

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

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

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