Search the values starting with char provided by user in postgresql function

Question

I have a requirement to search for the strings starting with char provided by user. I am using a postgresql function and returning SETOF-

CREATE OR REPLACE FUNCTION public._A1_loc_search15(in_pattern character varying) 
RETURNS setof character varying
AS
$$
SELECT coord FROM table1 WHERE loc_lb ~* '^in_pattern';
$$ 
LANGUAGE sql;

SELECT public._A1_loc_search15('A');

While the select query runs fine if hard coded '^A' is used, with user input it's not working. Any insight on how to use it correctly would help. Thanks.

SQB · Accepted Answer · 2013-11-20 07:22:30Z

1

It looks as if you're not actually using in_pattern as provided by the user, but just the literal string "in_pattern". So you're searching for /^in_pattern/.

answered Nov 20, 2013 at 7:22

SQB

4,1262 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3011913 Over a year ago

:using the suggested pattern does not works too..I am using postgresql 9.x,if this helps..earlier I also used '||^in_pattern||' simialr to a sql expression regexp_like but this doesnt works as well..

SQB Over a year ago

I'm sorry, I wasn't suggesting a pattern, I was merely suggesting that you're not using one.

Denis de Bernardy · Accepted Answer · 2013-11-20 08:31:40Z

0

You need to use the actual variable:

SELECT coord FROM table1 WHERE loc_lb ~* ('^' || in_pattern);

You could also use like (or ilike):

SELECT coord FROM table1 WHERE loc_lb like (in_pattern || '%');

answered Nov 20, 2013 at 8:31

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

Search the values starting with char provided by user in postgresql function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related