pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about functions
5+ messages / 3 participants
[nested] [flat]
* Question about functions
@ 2005-10-17 03:24 Mike Plemmons <mikeplemmons@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mike Plemmons @ 2005-10-17 03:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about functions
@ 2005-10-17 04:41 Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stewart Ben (RBAU/EQS4) * @ 2005-10-17 04:41 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about functions
@ 2005-10-17 08:17 Richard Huxton <dev@archonet.com>
parent: Mike Plemmons <mikeplemmons@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-10-17 08:17 UTC (permalink / raw)
To: Mike Plemmons <mikeplemmons@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about functions
@ 2005-10-17 08:22 Richard Huxton <dev@archonet.com>
parent: Mike Plemmons <mikeplemmons@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2005-10-17 08:22 UTC (permalink / raw)
To: Mike Plemmons <mikeplemmons@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question about functions
@ 2005-10-17 11:17 Richard Huxton <dev@archonet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-10-17 11:17 UTC (permalink / raw)
To: Mike Plemmons <mikeplemmons@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-17 11:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-17 03:24 Question about functions Mike Plemmons <mikeplemmons@gmail.com>
2005-10-17 08:17 ` Richard Huxton <dev@archonet.com>
2005-10-17 08:22 ` Richard Huxton <dev@archonet.com>
2005-10-17 11:17 ` Richard Huxton <dev@archonet.com>
2005-10-17 04:41 Re: Question about functions Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
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