Thread: Re: [pgsql-ru-general] Хранимая процедура: возврат строки разного формата

есть таблица

parent:
 | id | somecol |

есть десяток унаследованных таблиц:

childX:
 | id | somecol | .... |


есть некий скрипт, который обрабатывает все данные в унаследованных по
каким-то критериям.

работает примерно так:

1. делает
   SELECT
       parent.id,
       pg_class.relname AS tbl
   FROM
       parent, pg_class
   WHERE
       pg_class.oid = r.tableoid
   LIMIT 1;

2. если результаты есть делает
   SELECT
       %1 AS tbl,
       *
   FROM
       %1
   WHERE
       id = %2

   где вместо %1 подставляется поле  tbl выбранное из первого
   запроса, а вместо %2 - id.

После того как результаты обработаны они удаляются из унаследованной
таблицы.

вот хочется запхать 1 и 2 в хранимую процедуру, пишем:

CREATE OR REPLACE FUNCTION foo() RETURNS RECORD AS $$
   DECLARE
       retval RECORD;
       recid  RECORD;
   BEGIN
       SELECT
           p.id,
           pg_class.relname AS tbl
       INTO
           recid
       FROM
           parent, pg_class
       WHERE
           parent.tableoid = pg_class.oid
       LIMIT 1;

       EXECUTE
           'SELECT '''
               || recid.tbl
               || ''' AS tbl, * FROM "'
               || recid.tbl
               || '" WHERE id = '
               || recid.id
           INTO retval;
       RETURN retval;
$$ LANGUAGE 'plpgsql';

получаем примерно то что надо но в виде одного значения:

foo: '(tblname,1234,abc,...)'

а хочется получить в виде такого результата как вернул бы SELECT, то
есть в виде хеша:

tbl:        tblname
id:         1234
somecol:    'abc'
...

И еще, квоттинг: в документации написано что можно использовать
функции quote_ident и quote_value для создания динамических запросов,
но у меня почему-то постгрис на них ругается, говорит: нет такой
функции

--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

DI> Используйте SELECT * FROM foo();

в приведенном примере SELECT foo() работает,
а вот про
SELECT * FROM foo()

говорит ошибка:

ERROR:  a column definition list is required for functions returning "record"
SELECT * FROM foo()
             ^

понятное дело что определить column definition list заранее я не могу,
поскольку дочерние таблицы - разные.

и как быть?

DI> Если функции quote_ident(), quote_literal() и quote_nullable(). Первая
DI> принимает в кач-ве аргумента только текст, а вторая и третья перегружены.
DI> Проверьте аргумент какого типа Вы указываете при вызове.

ага поставил ::text и заработало, спасибо!
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

DI> Так псевдотип RECORD по своей сути не имеет предопределённой структуры,
DI> то необходимо явно её указывать (включая точный тип данных каждого столбца)
DI> в момент выборки.

то есть моя задача на хранимой процедуре решена быть не может :(

придется видимо оставлять два запроса

--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

DI> Смотря какая задача. Вашу, если честно, я до конца не понял.

ну попробую подробнее:

имеется родительская таблица:
 | id | somecol |

и имеется множество таблиц от нее произведенных.

Фишка родительской таблицы в том что она неявно адресует всех дочек.

несмотря на то что все дочерние таблицы - разные, алогоритмика их
обработки примерно одинакова:

выбрали из них данное, обработали (запись ушла в другую БД), стерли

таким образом запрос вида

SELECT * FROM parent;

дает ответ на вопрос "есть ли данные для обработки в хотя бы одной из
дочерних таблиц?" а если сджойнить его с pg_class то и попутно ответ
на вопрос "в какой таблице есть данные для обработки?"

далее зная что в какой-то таблице есть данные мы имея имя этой таблицы
и уже заранее предвыбранный id записи можем забрать запись из этой
таблицы на выборку. а потом удалить.

вот и получалось две стадии:

1. селект по родительской
2. селект по одной из дочерних

я хотел это все впихнуть в хранимую процедуру, но полный аналог
двум селектам сделать не получается: либо запись с кучей безымянных
полей на выходе либо эти имена надо вручную расставлять, а это более
накладно нежели два общих SQL запроса в клиенте.

PS: дочерние таблицы - данные поступающие от различных устройств
измерения. Они актуальны только пока не обработаны, после обработки
становятся не нужны и удаляются.

--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

DI> Хороший пример задачи, где можно применить hstore.
DI> Решение.
DI> CREATE TABLE device(id serial not null primary key, name text not null); --
DI> усройство
DI> CREATE TABLE measurement(id serial not null primary key,
DI> device integer not null references device(id),
DI> mtime timestamp not null default now(),
DI> data hstore not null) ; -- измерение

DI> Итого: 2 сущности - "устройство" и "измерение". Никаких объединений с pg_class.

кабы было все так просто то и не стоило бы огород городить :) я тут
просто задачу сильно упрощаю по отношению к реальной когда формулирую.

на самом деле данные не удаляются, а помечаются как обработанные это
раз.

а во вторых по каждой из дочерних таблиц построены свои индексы/отчеты
итп. то есть их не получается просто взять и в кучу соединить: как
минимум эффективные индексы потеряем (в т. ч. и уникальные)

то есть если мы перейдем к hstore, то получим способ записи/хранения,
но потеряем способы выборки. для хешей одного типа нужен индекс по
key1, для хешей другого типа по key2, для третьего - уникальный ключ
по key3 и key4, плюс еще CHECK'и а так же некоторые измерения имеют
друг на друга FOREIGN'ы.

можно было бы по добавлении записи в любую таблицу пересчет общей
статистики делать, но это накладно, поэтому вот такая схема как я выше
нарисовал используется
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

8 марта 2011 г. 0:39 пользователь Dmitry E. Oboukhov <unera@debian.org> написал:

DI> Хороший пример задачи, где можно применить hstore.
DI> Решение.
DI> CREATE TABLE device(id serial not null primary key, name text not null); --
DI> усройство
DI> CREATE TABLE measurement(id serial not null primary key,
DI> device integer not null references device(id),
DI> mtime timestamp not null default now(),
DI> data hstore not null) ; -- измерение

DI> Итого: 2 сущности - "устройство" и "измерение". Никаких объединений с pg_class.

кабы было все так просто то и не стоило бы огород городить :) я тут
просто задачу сильно упрощаю по отношению к реальной когда формулирую.

на самом деле данные не удаляются, а помечаются как обработанные это
раз.

а во вторых по каждой из дочерних таблиц построены свои индексы/отчеты
итп. то есть их не получается просто взять и в кучу соединить: как
минимум эффективные индексы потеряем (в т. ч. и уникальные)

Данные hstore индексируются с помощью GIST или GIN.

 

то есть если мы перейдем к hstore, то получим способ записи/хранения,
но потеряем способы выборки. для хешей одного типа нужен индекс по
key1, для хешей другого типа по key2, для третьего - уникальный ключ
по key3 и key4, плюс еще CHECK'и а так же некоторые измерения имеют
друг на друга FOREIGN'ы.

Уникальные ключи в измерениях? Можно пример, очень интересно.
А ещё интереснее было бы взглянуть на пример с вн. ключами.
 

можно было бы по добавлении записи в любую таблицу пересчет общей
статистики делать, но это накладно, поэтому вот такая схема как я выше
нарисовал используется
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

--
// Dmitriy.

DI> Данные hstore индексируются с помощью GIST или GIN.

а я вот с этими GIST и GIN не понял пока. где почитать про их
внутреннее устройство? интересует вопрос: если мы индекс по полю
hstore сделаем то насколько эффективность поиска пострадает по
сравнению с BTREE по выделенному столбику?

ну и некоторые столбики у меня бывает хранят массив значений (2-3)
то есть получится как бы хеш, но который в одном из своих полей (не
ключей) хранит массив. hstore справится с таким заданием? а построение
индекса по полю?

DI> Уникальные ключи в измерениях? Можно пример, очень интересно.

есть и уникальные. фишка в том что некоторые измерители выдают
повторяющиеся результаты, а так же делают повторы передач если
посчитали что коннект с сервером не состоялся (канал плохой бывает
всякое)

соответственно есть уникальные индексы вида

 идентификатор-устройства -- время когда было сделано измерение

на таблицу написано RULE, которое при приходе измерения которое уже
приходило ранее делает игнор (вернее запись в таблицу регистрации
трабел)

DI> А ещё интереснее было бы взглянуть на пример с вн. ключами.

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

ну и комбинации вышеприведенного
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
 `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537