PostgreSQL, refcursor и проблема 1+N запросов

[metaclass]

Решил я по совету использовать курсоры, чтобы вернуть результат нескольких запросов в одном резалтсете. Ну то есть решить проблему 1+N запросов.

Сделал такой примерчик
ссылко1
ссылко2, скрипт
По идее, на каждую строку результсета должен вернутся открытый курсор, то бишь, мы фетчим внешний резалтсет, а внутри цикла фетча пытаемся фетчить полученный курсор.

На данный момент пока хрен что у меня получается, т.к. все компоненты доступа(и в дельфи, и в дотнете) возвращают первое поле запроса результат запроса "select * from myfunc() as test(a refcursor,b varchar(40))" видят тупо как строку и ни с какого бока к этому курсору не подступится.

Синтаксис работы с курсорами и возвращения множеств из функций postgresql тоже в некотором роде печалит, но это следствие видимо того, что я привык в этому плане к аккуратному синтаксису Firebird.

PS: Кстати, что забавно: в примерах везде приведены только самые простые случаи типа "возвращаем один курсор, возвращаем два заведомо известных курсора", а вот комбинацию "возвращаем множество туплов из курсора и атомарного типа" пришлось придумывать самому, с извращениями в виде create type.

PPS: Гамон. Победил эту хрень. исправленный скрипт (отличия - именование курсора и убрано его закрытие)
1)курсоры нужно именовать уникально, типа
cur='returnedcursor' || cast(parentid as varchar);
2)курсоры не нужно закрывать, т.к. второй запрос выполняется только после того как целиком выполнится первый, а не внутри него, как хотелось бы.
3)возвращенное в первом запросе имя курсора нужно использовать, чтобы обратится к его результатам:
Delphi: 'fetch all from '+q.Fields[0].AsString;
.NET: cmd2.CommandText = "FETCH ALL FROM "+reader1.GetString(0);
4)в дотнете это работает только с PreloadReader=true, иначе ругается что "уже открыт DataReader", я про это уже писал, что только Firebird умеет открывать более одного ридера.

В общем, те же яйца, вид в профиль.
Не знаю, как насчет парсинга запроса и кэширования результата парсинга, запросто может быть, что оно каждый раз во внутреннем цикле это делает повторно, то бишь опять та же проблема 1+N, только стоя и в гамаке.
Кроме того, сразу видно, что PL/pgSQL это ад, т.к. присваивая переменной курсора имя, мы делаем это имя до закрытия курсора доступным в FETCH ALL FROM, то есть, явное смешение данных и метаданных. Это можно было бы считать метапрограммированием, но это больше похоже на динамическую типизацию(анафема!!).

Comments

[plumqqz.livejournal.com]

Проще надо быть, проще:

CREATE OR REPLACE FUNCTION get_govnoandgaz()
  RETURNS refcursor AS
$BODY$
declare
 wss_cr refcursor;
 gaz_cr refcursor;
 pss_cr refcursor;
 sss_cr refcursor;
 ct_cr refcursor;
 pl_cr refcursor;
 cpt_cr refcursor;
 vt_cr refcursor;
 rv refcursor;
 commpt_cr refcursor;
 contr_cr refcursor;
 fct_cr refcursor;
 lav_cr refcursor;
 win_cr refcursor;
 bal_cr refcursor;
 yo_cr refcursor;
 
begin
 open wss_cr for select id, name from water_supply_state where orderer is not null order by orderer;
 open gaz_cr for select id, name from gas_supply_state where orderer is not null order by orderer;
 open pss_cr for select id, name from power_supply_state where orderer is not null order by orderer;
 open sss_cr for select id, name from sewerage_supply_state where orderer is not null order by orderer;
 open ct_cr for select id, name from country_type where orderer is not null order by orderer;
 open pl_cr for select id, name from place where parent_id=3 order by name;
 open cpt_cr for select id, name from country_property_type where orderer is not null order by orderer;
 open vt_cr for select id, name from village_type order by name;
 open commpt_cr for select id, name as name from commercial_property_type order by orderer;
 open contr_cr for select id, name from contract_type where orderer is not null order by orderer;
 open fct_cr for select id, name from flat_condition_type where orderer is not null order by orderer;
 open lav_cr for select id, name from lavatory_type where orderer is not null order by orderer;
 open win_cr for select id, name from windows_type where orderer is not null order by orderer;
 open bal_cr for select id, name from balcony_type where orderer is not null order by orderer;
 open yo_cr for select id, name from years_owned_type where orderer is not null order by orderer;
 
 open rv for select wss_cr as water_supply_states,
                    gaz_cr as gas_supply_states,
                    pss_cr as power_supply_states,
                    sss_cr as sewerage_supply_states,
                    ct_cr as country_types,
                    pl_cr as places,
                    cpt_cr as country_property_types,
                    vt_cr as village_types,
                    commpt_cr as commercial_property_type,
                    contr_cr as contract_type,
                    fct_cr as flat_condition_type,
		    lav_cr as lavatory_type,
		    win_cr as windows_type,
		    bal_cr as balcony_type,
		    yo_cr as years_owned_type,
                    (select value from currency.currency where symb='USD' order by added desc limit 1) as usd_rate;
 return rv;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_govnoandgaz() OWNER TO postgres;

[metaclass.livejournal.com]

Это ж не nested курсоры, тут возвращается одна запись с полями-курсорами.

[plumqqz.livejournal.com]

Тут возвращается один курсор с полями-курсорами. Этим курсорам тоже никто не мешает иметь поля-курсоры и так далее.

[metaclass.livejournal.com]

Т.е. разницы между статической структурой из курсоров и курсором внутри курсора не ощущаете?

[plumqqz.livejournal.com]

Я вообще не понимаю, о чем речь. Я предложил способ (вполне простой, удобный и очевидный на мой взгляд, по крайней мере) формирования сколь угодно развесистого дерева из курсоров - а уж как оно там набивается - вопрос второй. Причем тут разница между статической структурой и курсором внутри курсора?

[metaclass.livejournal.com]

Разница в том, что я скроллирую один курсор и на каждую запись в нем создаю второй - из записей в подчиненной таблице. Условно говоря "список накладных и для каждой накладной-список строк в ней".
Простейшая операция, но почему-то везде, кроме дельфей тянущая за собой какое-то невменяемое вуду.
Т.е. препарим два запроса, выполняем первый, фетчим его, в процессе фетча подставляем значение поля из первого запроса в качестве параметра для второго, выполняем и во внутреннем цикле фетчим второй запрос.
Очевидно, желательно чтобы запроы препарились-парсились один раз - в начале всей операции, и чтобы потребление памяти сервером и клиентов было O(1).
В Firebird в таком случае сервер создает два запроса, на клиенте держатся их хендлы, которые можно использовать для установки параметров, выполнения и фетча.
В Postgresql параметры в запрос подставляет почему-то клиентская либа, на каждое внутреннее выполнение посылая новый текст запроса. Умеет он ли при этом заменить литерал на параметр и использовать ранее скэшированный результат препаре - вопрос сложный.
В MSSQL и дотнете такое вообще без дополнительного параметра Multiple Active Result Sets = True не работает, и этот параметр тянет за собой какие-то другие проблемы.

[plumqqz.livejournal.com]

Разница в том, что я скроллирую один курсор и на каждую запись в нем создаю второй - из записей в подчиненной таблице. Условно говоря "список накладных и для каждой накладной-список строк в ней".

Ну о чем и речь. В постгресе аж два варианта:

1.

select t.val1, cursor_returning_function(t.val2)
from table t

2.

select t.val1, array(select val3 from .... where t.valN=...)
from table t

В оракле один, с одной стороны чуть поудобнее, с другой - чуть понеудобнее, оракловые курсоры в резалтсете специфичней:

select t.val1, cursor(select ... from ... where t.valN=...) 

Разумеется, и в том и в другом случае в результирующих курсорах могут быть другие курсоры и т.д.

Я активно использую и постгресовый, и оракловый вариант и очень доволен.

[metaclass.livejournal.com]

Вроде про первый вариант с постгресом в документации написано, что он deprecated и его в любой момент могут выпилить.

[plumqqz.livejournal.com]

Вроде про первый вариант с постгресом в документации написано, что он deprecated и его в любой момент могут выпилить.

Это где там такое написано?

[metaclass.livejournal.com]

А, перепутал с setof функциями:
"Currently, functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and might be removed in future releases."