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:
Here the second quote is not taken as part of the string, but the end of it. The parser then sees
jhonand 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:
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.