Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with LIKE in a SQL function

Problem with LIKE in a SQL function

From
Mole
Date:
Hello,
    I have a bit of a problem, I have looked on the archives and didn't manage
to see anything.

I am trying to write the below function

mail_db=# CREATE FUNCTION "user_id_funct_p2"(varchar)
mail_db-# RETURNS setof varchar AS '
mail_db'# SELECT "mailbox" FROM "user" WHERE "mailbox" LIKE (\'$1%\') '
mail_db-# LANGUAGE sql ;
CREATE

mail_db=# select user_id_funct_p2('test');
 user_id_funct_p2
------------------
(0 rows)

If I  do
  SELECT "mailbox" FROM "user" WHERE "mailbox" LIKE ('test%') ;

It does returns the following results

 mailbox
----------
 test0001
 test0002
 test0003
 test0004
 test0005
 test0006
(6 rows)

If I ammed the function to return $1 it does return as test.  When I looked @
the debug output it seemed to be matching $1 rather that the value of $1.
That leads me to believe that my character escape sequence is incorrect.  I
have tried every possible combination but I am still not returning valid
results.

Many thanks in advance

Mole
--
==============
Caroline Fletcher
Head of Systems

Re: Problem with LIKE in a SQL function

From
Tom Lane
Date:
Mole <mole@zebra.co.uk> writes:
> mail_db=# CREATE FUNCTION "user_id_funct_p2"(varchar)
> mail_db-# RETURNS setof varchar AS '
> mail_db'# SELECT "mailbox" FROM "user" WHERE "mailbox" LIKE (\'$1%\') '
> mail_db-# LANGUAGE sql ;

I think you want:

SELECT "mailbox" FROM "user" WHERE "mailbox" LIKE ($1 || \'%\') '

            regards, tom lane

Re: Problem with LIKE in a SQL function

From
Garrett Bladow
Date:
I think this does the trick.

CREATE FUNCTION user_id_funct_p2(VARCHAR) RETURNS setof varchar AS '
    SELECT mailbox FROM user WHERE mailbox LIKE (SELECT ($1 || \'%\'));
'LANGUAGE sql ;

-Garrett

---- This is what you wrote me ----

:Hello,
:    I have a bit of a problem, I have looked on the archives and didn't manage
:to see anything.
:
:I am trying to write the below function
:
:mail_db=# CREATE FUNCTION "user_id_funct_p2"(varchar)
:mail_db-# RETURNS setof varchar AS '
:mail_db'# SELECT "mailbox" FROM "user" WHERE "mailbox" LIKE (\'$1%\') '
:mail_db-# LANGUAGE sql ;
:CREATE
:
:mail_db=# select user_id_funct_p2('test');
: user_id_funct_p2
:------------------
:(0 rows)
:
:If I  do
:  SELECT "mailbox" FROM "user" WHERE "mailbox" LIKE ('test%') ;
:
:It does returns the following results
:
: mailbox
:----------
: test0001
: test0002
: test0003
: test0004
: test0005
: test0006
:(6 rows)
:
:If I ammed the function to return $1 it does return as test.  When I looked @
:the debug output it seemed to be matching $1 rather that the value of $1.
:That leads me to believe that my character escape sequence is incorrect.  I
:have tried every possible combination but I am still not returning valid
:results.
:
:Many thanks in advance
:
:Mole
:--
:==============
:Caroline Fletcher
:Head of Systems
:
:---------------------------(end of broadcast)---------------------------
:TIP 4: Don't 'kill -9' the postmaster
:

Re: Problem with LIKE in a SQL function

From
Mole
Date:
Hi All,

many thanks to Garret and Tom, that did the trick.

"SELECT mailbox FROM user WHERE mailbox LIKE (SELECT ($1 || \'%\')); "

Mole
--
==============
Caroline Fletcher
Head of Systems
Zebrahosts