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

From Schuhmacher, Bret
Subject Re: Problems with PL/pgSQL and LIKE statement
Date
Msg-id 98E4F4D46DACD0479C96D7356D5C37356B0370@sac1exch3.aspect.com
Whole thread Raw
In response to Problems with PL/pgSQL and LIKE statement  ("Paul Murphy" <pmurphy@bloxx.com>)
List 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@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Paul Murphy
> Sent: Thursday, January 20, 2005 11:21 AM
> To: pgsql-novice@postgresql.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@postgresql.org so
> that your
>       message can get through to the mailing list cleanly
>

pgsql-novice by date:

Previous
From: "Subha"
Date:
Subject: Postgresql on windows
Next
From: "Mike G."
Date:
Subject: Re: Windows 2000 Pro Error on Install