|
|
 | | From: | Paul Murphy | | Subject: | Problems with PL/pgSQL and LIKE statement | | Date: | Thu, 20 Jan 2005 16:20:43 -0000 |
|
|
 | Hi all,
I'm writing a function that basically returns all of the URLs that are
like (in the SQL sense of LIKE) a string that I supply. I can't seem to
get the correct arrangement of percent symbols and quotes to get the
statement to run correctly.
Here's a simplified version of what I'm trying to do...
CREATE OR REPLACE FUNCTION get_urls_like(varchar)
RETURNS varchar AS
'DECLARE
param ALIAS FOR $1;
entry varchar;
BEGIN
FOR entry IN SELECT url AS url
FROM urls u
WHERE url LIKE ''''%'''' || param || ''''%''''
LOOP
RETURN NEXT entry;
END LOOP;
RETURN;
END;'
LANGUAGE 'plpgsql' STABLE;
Any help gratefully received. I've tried various combinations of percent
signs and single quotes and usually end up with errors like the
following.
ERROR: operator is not unique: "unknown" % "unknown"
HINT: Could not choose a best candidate operator. You may need to add
explicit type casts.
CONTEXT: PL/pgSQL function "get_classifications_like" line 6 at for
over select rows
Cheers
Paul
Paul Murphy
Senior Software Engineer
Packet Dynamics Ltd
tel: +44 (0)1506 426 976
fax: +44 (0)1506 418 844
pmurphy at bloxx dot com
Call 08700 4 BLOXX or visit www.bloxx.com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
|
