Problems with PL/pgSQL and LIKE statement - Mailing list pgsql-novice

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