Dynamic query return

Поиск
Список
Период
Сортировка
От Mat Arye
Тема Dynamic query return
Дата
Msg-id CADsUR0CFuZc9_O+2GajoXsOXFnxCits+DF+HZ1uMpRbxviWZTA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Dynamic query return  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Raymond O'Donnell
Дата:
Сообщение: Re: Installing 9.6 RC on Ubuntu
Следующее
От: Alex Sviridov
Дата:
Сообщение: pgAdmin3 backup over ssh tunnel