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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2