Can you add autocomplete in strings to write dynamic queries?
It is a complex task. Strings can be built by multiple operators, for example (PostgreSQL; sorry, I can't take a screenshot):
_str text;
_str = 'SELECT * FROM public.test t';
IF _use_table_one THEN
_str = ' LEFT JOIN public.table_two tt ON tt.id=t.col_two';
ELSE
_str = ' RIGHT JOIN public.table_two tt ON tt.id=t.col_two';
END IF;
IF _in_filter IS NOT NULL THEN
_str = _str || ' WHERE t.filter_column = '''|| _in_filter ||'''';
END IF;
RETURN QUERY EXECUTE _str;
Please sign in to leave a comment.
Yes, Alt+Enter -> Inject language -> PostgreSQL
@Maxim Sobolevskiy
Such injection does not work properly when string is interrupted by regular code. Each fragment of string is treated as whole SQL statement. For example, 1 string
_str = 'SELECT * FROM public.test t' || _where || 'ORDER BY t.ord';
is splitted on 2 fragments.
I am getting error "statement expected, got ORDER" on "ORDER BY".
Moreover, in most cases when some string is not splitted you can use regular code instead of the dynamic.
Well, the workaround is to make this injection manual with the help of comments. Please, put prefix and suffix to make this injected statement "complete". It will be treated as a correct code and can be completed.
Example: