From: | "Paul Murphy" <pmurphy(at)bloxx(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Problems with PL/pgSQL and LIKE statement |
Date: | 2005-01-21 11:07:10 |
Message-ID: | 3C129FC9A1BC9446921D3D2B2BBEB076120923@sbs2003.PacketDynamics.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi all,
I've got it solved - I was using PgAdmin III to enter the function, and
the escaping of quotes it uses seems to have been the problem (or my
understanding of how it escapes quotes...). When I switched to using
psql, I tried the syntax that Brett suggested and the function works
fine.
FYI, the syntax that Brett suggests below gets displayed as
...LIKE \'%\' || param || \'%\'
in PgAdmin III.
Cheers
Paul
-----Original Message-----
From: Schuhmacher, Bret [mailto:Bret(dot)Schuhmacher(at)Aspect(dot)com]
Sent: 21 January 2005 04:58
To: Paul Murphy; pgsql-novice(at)postgresql(dot)org
Subject: RE: [NOVICE] Problems with PL/pgSQL and LIKE statement
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2005-01-21 11:08:46 | Re: hoe to connect postgres database thru perl |
Previous Message | Shaun McGuile | 2005-01-21 08:09:18 | Re: Windows 2000 Pro Error on Install |