So let's say I have a function that takes in one parameter
create function(i_param VARCHAR)
I want to do a select one the table WHERE col_name LIKE 'i_param%'
I tried doing LIKE i_param||'%' but I think it literally returns the string 'i_param' not the value put into the function.
It doesn't give me any errors but returns zero rows. How would I do a LIKE on the input parameter?
Pretty new to this stuff, so any help would be great! Thanks!
As a note, starting in 9.2, you can do things the way you are. In previous versions, you should get an error instead.
The fact that you didn't get an error suggests that your use of i_param is in colliding with a column name. I recommend prefixing input variables for this reason (I use "in_" in my work).
For example, if you:
CREATE TABLE foo (
foo text,
bar text
);
Then the following will not generate an error:
CREATE FUNCTION search_foo(bar text) RETURNS SETOF foo LANGUAGE SQL AS $$
SELECT * FROM foo WHERE foo ilike bar || '%';
$$;
However this will in fact be comparing the column foo against the column bar. The following will give you errors:
CREATE FUNCTION search_foo(in_bar text) returns setof foo language sql as $$
SELECT * from foo where foo ilike in_bar ||'%';
$$;
I highly recommend avoiding variables with the same names as arguments. That leads to problems in many places.
LIKE i_param||'%'is not working than you are not showing us everything. Can you post a complete example that shows that it is not working?