Like statement in Function

[iaresean]

Hi all;

I am trying to creat a function that will search my zones table for any zone that contains the text I am passing to the function. I realise I have to use % as the wildcard before and after my variable, but I can't get the below to work. I have tried "WHERE zone LIKE ('%' + $1 + '%');" but that doesn't work. "WHERE zone LIKE $1;" works but that means it needs an exact match. I have just about no postgres experience. I downloaded the guide from the postgres site but I am struggling to find the solution to my problem.

CREATE OR REPLACE FUNCTION searchForDomain(TEXT)
    RETURNS SETOF TEXT AS
'
        SELECT zone
            FROM zones
             WHERE zone LIKE '%$1%';
'

Any help would be greatly appreciated.

Thanks.

Sean.

 

[ceco]

the problem is in that you should escepe the ' with one more ' ;-))

i.e. ''

CREATE OR REPLACE FUNCTION searchForDomain(TEXT)
RETURNS SETOF TEXT AS
'
SELECT zone
FROM zones
WHERE zone LIKE ''%$1%'';
'

 

[iaresean]

Thanks for your helpful and quick response Ceco!

Although you helped me move forward it wasn't the complete solution. I also had to seperate the variables and concatenate them together. Otherwise the search wouldn't be performed properly, and would never return any matches. Here is the working solution to my problem:

CREATE OR REPLACE FUNCTION searchForDomain(TEXT)
    RETURNS SETOF TEXT AS
'
        SELECT zone
            FROM zones
             WHERE zone LIKE (''%'' || $1 || ''%'');
' LANGUAGE 'sql' SECURITY DEFINER;

Thanks once again;

Sean.