Stack Exchange
Passing dynamic filter to the Postgresql stored procedure
I have a stored procedure like this.
create or replace function filter_data_student(filter_conditions text)
   RETURNS VOID
   language plpgsql
AS $function$
DECLARE
    v_sql text default '';
BEGIN
    V_SQL = 'CREATE TEMP TABLE TEST ON COMMIT DROP AS 
            SELECT *
            FROM STUDENT 
            where '||filter_conditions||'
            ';
            execute V_SQL;
            return;
end;
$function$

I should be able to call this procedure as below, but getting syntax error while calling.
select * from filter_data_student('name='jhon'') 
select * from filter_data_student('name in ('jhon', 'Jerry') and id='100'')


Your issue is trying to use single quotes within a string:

'name='jhon''


Here the second quote is not taken as part of the string, but the end of it. The parser then sees jhon and doesn't know what to do with it.
To include a single quote in a string you need to "escape" it with another quote, so that example becomes:

'name=''jhon'''


Note the three single quotes at the end: the first two represent a quote in the string value, the last terminates the string as it is not escaped by another.
In other languages you see back-slash characters used as escapes, so \' for a quote, and with certain options that works in postgres but doubling up quotes is the standard way for SQL.
As a side note: be very careful when producing ad-hoc SQL this way, you will find it very easy to open you code up to "SQL injection" attacks. If you are not already aware of this danger, I suggest you look it up before continuing down this path.

I'd say that with a procedure like that, there is no way to avoid the danger of SQL injection.

@LaurenzAlbe - I would say so too, unless: the procedure can only be called by the right code (permissions set in a paranoid manner to lock it down), the SQL fragment being passed in is always generated just before being passed not being read from anywhere (even your DB), and all its constituent parts that have to come from elsewhere (the names in this case) are very carefully validated.
