Write like and not like dynamically in plpgsql

Question

SQL1:

select regno from student where regno **like 'ABCD%'**

This is running successfully. But how can I write like 'ABCD%' dynamically?
For example:

CREATE OR REPLACE FUNCTION check_regno(refcursor, character varying)
RETURNS refcursor AS
$BODY$
begin
select regno from student where regno $1
return $1;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Now I want to pass $1 as like 'ABCD%' i.e.:

select check_regno(f1, "like 'ABCD%'")

This will give error at $1:

Please suggest how to achieve this.

Answer 1

As @Igor mentioned, this is error prone. I would go one step further: Don't do it. You invite SQL injection. Consider this related answer on dba.SE.

In fact, I don't see anything in your question warranting dynamic SQL at all. Use a plain SQL function and pass a plain string value instead:

CREATE OR REPLACE FUNCTION check_regno(_like bool, _filter text)
  RETURNS SETOF text AS
$func$
SELECT regno FROM student
WHERE  CASE WHEN $1 THEN regno ~~ $2 ELSE regno !~~ $2 END
$func$ LANGUAGE sql;

~~ and !~~ being Postgres operators for LIKE and NOT LIKE (you can use either).

Call:

SELECT * FROM check_regno(TRUE, 'ABCD%');
SELECT * FROM check_regno(FALSE, 'DEFG%');

Answer 2

Try something like:

CREATE OR REPLACE FUNCTION check_regno(p_filter varchar)
RETURNS SETOF student.regno%TYPE AS
$BODY$
begin
  return query execute 'select regno from student where regno'||p_filter;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

SELECT * FROM check_regno('like ''ABCD%''');

But this type of dynamic SQL is error prone and can allow SQL injections.