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

Re: Problems with PL/pgSQL and LIKE statement

From: "Schuhmacher, Bret" <Bret(dot)Schuhmacher(at)Aspect(dot)com>
To: "Paul Murphy" <pmurphy(at)bloxx(dot)com>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Problems with PL/pgSQL and LIKE statement
Date: 2005-01-21 04:57:52
Message-ID: 98E4F4D46DACD0479C96D7356D5C37356B0370@sac1exch3.aspect.com (view raw or flat)
Thread:
Lists: pgsql-novice
This works for me:
...like ''%'' || b.vendor_name || ''%''

Looks like you have too many single quotes...

Rgds,

Bret  

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org 
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Paul Murphy
> Sent: Thursday, January 20, 2005 11:21 AM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: [NOVICE] Problems with PL/pgSQL and LIKE statement
> 
> 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(at)postgresql(dot)org so 
> that your
>       message can get through to the mailing list cleanly
> 

pgsql-novice by date

Next:From: Mike G.Date: 2005-01-21 05:06:55
Subject: Re: How to "COPY schema1.table TO schema2.table" ?
Previous:From: Mike G.Date: 2005-01-21 04:53:32
Subject: Re: Windows 2000 Pro Error on Install

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