Hi All,
I have a project where I wrote custom plpgsql functions to do specialized queries of my dataset. These functions dynamically generate sql and then RETURN EXECUTE that generated sql. From the client perspective the usage looks like:
SELECT * FROM exec_query(new_query_object(param1 => 'blah', param2 =>'foo'))
The number and types of columns returned are dynamic. So exec_query has to return a type RECORD. This creates a problem because now I have to call it as:
SELECT * FROM exec_query(new_query_object(param1 => 'blah', param2 =>'foo')) as res(column_1 type_1, column_2 type_2,..)
That is a much uglier interface. Also doesn't work for queries where the client does not know the column set ahead of time (think select * queries). I have a few solutions but was wondering if I was missing some functionality. The solutions I have are:
1) return one column with json. The problem is row_to_json, to_json and to_jsonb all have pretty high overhead in my tests.
2) have exec_query create a temporary table. Then do a select * from the temporary table. Problem is two client side queries. Plus, I think the overhead for creating temporary tables is not trivial for low latency queries.
3) Create a function that gives back sql code to execute. I.e.
SELECT code FROM sql_exec_query(new_query_object(param1 => 'blah', param2 =>'foo'));that returns the string 'SELECT * FROM exec_query(new_query_object(param1 => 'blah', param2 =>'foo')) as res(column_1 type_1, column_2 type_2,..)'
Then you execute this returned string. Problem here is two client-side queries. Is there a way to do this all (code generation and execution) server-side?
I am leaning towards solution 3 for now. But it's kind of a hack. Is there any better solution that I am missing?
Thanks in advance.
Thanks,
Mat
