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

Search Discussions

  • Schuhmacher, Bret at Jan 21, 2005 at 4:58 am
    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
  • 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 20, '05 at 4:20p
activeJan 21, '05 at 11:07a
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Paul Murphy: 2 posts Schuhmacher, Bret: 1 post

People

Translate

site design / logo © 2022 Grokbase