Re: Problem with LIKE in a SQL function

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
: