Skip site navigation (1) Skip section navigation (2)

Problems with PL/pgSQL and LIKE statement

From: "Paul Murphy" <pmurphy(at)bloxx(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Problems with PL/pgSQL and LIKE statement
Date: 2005-01-20 16:20:43
Message-ID: 3C129FC9A1BC9446921D3D2B2BBEB07612091B@sbs2003.PacketDynamics.local (view raw or flat)
Thread:
Lists: 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 	


pgsql-novice by date

Next:From: Sean DavisDate: 2005-01-20 16:30:31
Subject: Re: Strange User Problem
Previous:From: Sean DavisDate: 2005-01-20 16:18:31
Subject: Re: postgresql log file on fc2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group