 |
Paul Murphy |
at Jan 21, 2005 at 11:07 am
|
⇧ | 
| |
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
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
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
