Thread: 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
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 >
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.Schuhmacher@Aspect.com] Sent: 21 January 2005 04:58 To: Paul Murphy; pgsql-novice@postgresql.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@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 >