Closed nosshar closed 3 years ago
Also I want to explain why I need this functionality. In my case PosgreSQL stores dictionary tables, but they contains JSONB columns. It's not possible to select from such a table via JDBC bridge, because such an exotic type is not suported by the bridge. It's possible to cast these columns to any string type, but in this case PosgreSQL indexes are not used at all and all the processing should be done in CH (what in addition gives an overhead because all the dataset is trasferred to CH on every call). So I tried to use VIEW (which work with flattened JSON via jsonb_to_record), it works but it's not customizable because has to explicitly define which JSONB keys should be flattened.
The function approach could give enough space to make any complicated logic work based on input parameters and inner logic of a function.
Try code on master branch and it will work. I'll close this one after 2.0 release.
Works in 2.0.0:
select * from jdbc('postgresql?mutation', '
CREATE OR REPLACE FUNCTION tst(input_parameter VARCHAR) RETURNS TABLE (result VARCHAR)
AS ''SELECT $1::VARCHAR AS test;''
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
')
SELECT * FROM jdbc('postgresql', 'select tst(''test'')')
Tried to make following scenario work and didn't succeed.
PostgreSQL: CREATE OR REPLACE FUNCTION tst(input_parameter VARCHAR) RETURNS TABLE (result VARCHAR) AS 'SELECT $1::VARCHAR AS test;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;
ClickHouse: SELECT * FROM jdbc('postgresql://192.168.242.1:5431/?user=CR_TEST&password=CR', 'public', "tst('test')")
What made me think it might work? Without function created CH raises an error: function public.tst(unknown) does not exist? Hint: No function matches the given name and argument types. But when function exists I got: relation "public.tst('test')" does not exist. Something is definitely trying to make a call to the entity/function.