Из личных наблюдений.
Из Postgres на Oracle и из Oracle на Postgres люди мигрируют без особого дискомфорта.
Про направление MySQL -> PG уже не раз говорили выше.
PG -> MySQL… Лично знаю пару человек, которым при переходе на другую работу пришлось пересесть с PG на MySQL. Цитировать высказывания не буду, ибо забанят, но скажу, что оба переживают такую перемену весьма болезненно.
Теперь без json.
Дошло, что для разворачивания записи прекрасно подойдет та же coalesce — она может принять аргумент любого типа (в данном случае типа record) и вернуть его без изменений, а это именно то, что нужно:
select r.*
from (values (0, 'Полковник', 'кот', null::integer)) d
left join election e
on e.votes > 10000,
coalesce(case when e.tableoid is null
then row(d.*)
else row(e.*)
end) r (id integer, name text, profession text, votes integer)
order by e.votes desc;
Пояснения к решению.
Идея в том, чтобы сначала свернуть запись в один столбец, к которыму можно применить CASE только один раз, а после получения нужной записи развернуть её обратно по столбцам. Функция row_to_json сворачивает запись, функция json_populate_record возвращает значение композитного типа, а если функция, возвращающая композитный тип, вызвана в секции FROM, то postgres разворачивает результат по отдельным столбцам. Но так получается только с функциями. Если обернуть функцию в подзапрос, то есть, вместо json_populate_record(...) написать LATERAL (SELECT json_populate_record(...)), запись не развернётся, а будет выведена как композитный столбец.
Запись можно было свернуть в столбец и без привлечения json, например, «SELECT ROW(election.*) AS e_record FROM election» выведет записи из election в один композитный столбец e_record. То есть, для того, чтобы свернуть запись можно использовать выражение ROW на месте функции row_to_json. Но вот функции, которая просто возвращала бы запись и которую можно было бы вызвать в секции FROM, чтобы запись развернулась, я не нашел.
Не уверен, что изложил понятно. Сам, наверное, не понял бы, если бы не был в теме :)
Экзотический вариант с применением JSON для PostgreSQL, начиная с 9.3:
select j.*
from (select '{"id":0,"name":"Полковник","profession":"кот","votes":null}'::json as j) d
left join election e on e.votes > 10000,
json_populate_record(null::election, case when e.tableoid is null then d.j else row_to_json(e.*) end) j
order by e.votes desc;
В PostgreSQL NVL2 нет — это расширение Oracle, как и NVL. Их и в стандарте нет. NVL-то ладно, она заменяется стандартной COALESCE, а вот NLV2, кроме громоздого CASE, по-моему, ничем не заменить. Кстати, про NVL2 до этого вообще не слышал. По тексту не догадался, как она работает, а в документации прочитал только что. Очень удобная функция, хотелось бы видеть подобную в стандарте. Или, может быть, я не вкурсе, и аналог в стандарте все-же есть. В любом случае в PostgreSQL аналога не нашел.
А с COALESCE да, про NULL в отдельных полях не подумал, надо менять на CASE.
Насчет rowid. В таблицах postgres семь штук системных столбцов, один из которых, oid, как раз аналог rowid, и до версии 8.1 oid в пользовательских таблицах создавался по умолчанию. Но так как это 32-битное целое, в больших и долгоживущих бд он уже не гарантирует уникальность, поэтому с версии 8.1 oid-ы есть только в системных таблицах, а в пользовательских по умолчанию не создаются. Хотя можно при создании таблицы указать WITH OIDS или вообще вернуть oid-ы по умолчанию в настройках сервера.
Но для проверки существования строки можно взять и другой системный столбец, например tableoid:
select
case when e.tableoid is null then d.id else e.id end as id,
case when e.tableoid is null then d.name else e.name end as name,
case when e.tableoid is null then d.profession else e.profession end as profession,
case when e.tableoid is null then d.votes else e.votes end as votes
...
Только теперь стало громоздко и лично я выбирал бы первый вариант :)
select
coalesce(e.id, d.id) as id,
coalesce(e.name, d.name) as name,
coalesce(e.profession, d.profession) as profession,
coalesce(e.votes, d.votes) as votes
...
Решения на PostgreSQL в принципе те же, только в запросах нет возможности использовать переменные. И конструкцию с model увидел впервые только сегодня (нет, уже вчера), поэтому вряд ли в postgres есть что-то похожее.
Способы 1 и 2 работают с минимальными изменениями — :bound нужно заменить на конкретное значение, а во втором способе плюс к этому просто удалить «from dual».
Аналог способа 3:
select
coalesce(e.id, d.id),
coalesce(e.name, d.name),
coalesce(e.profession, d.profession),
coalesce(e.votes, d.votes)
from (values (0, 'Полковник', 'кот', null::integer)) d (id, name, profession, votes)
left join election e on e.votes > 10000
order by e.votes desc;
или для удобочитаемости можно вынести виртуальную таблицу d из подзапроса в with:
with d (id, name, profession, votes) as (
values (0, 'Полковник', 'кот', null::integer)
)
select
coalesce(e.id, d.id),
coalesce(e.name, d.name),
coalesce(e.profession, d.profession),
coalesce(e.votes, d.votes)
from d
left join election e on e.votes > 8000
order by e.votes desc;
«values (0, 'Полковник', 'кот', null::integer)» везде можно заменить на аналогичный select, только придется явно указать тип для текстовых полей, зато имена колонкам можно задать на месте через as. Пример с with:
with d as (
select
0 as id,
'Полковник'::text as name,
'кот'::text as profession,
null::integer as votes
)
select
coalesce(e.id, d.id),
coalesce(e.name, d.name),
coalesce(e.profession, d.profession),
coalesce(e.votes, d.votes)
from d
left join election e on e.votes > 8000
order by e.votes desc;
В Голливуд как раз только-только запостил :)
Открыл для себя конструкцию ROWS FROM. Спасибо еще раз :)
Правда, все равно несколько громоздко получилось.
По выборам пока пытаюсь что-нибудь покрасивее соорудить, может уже завтра.
И я все-же больше по postgres, а в таких задачках приходится использовать инструменты, специфичные для конкретной СУБД.
Хотя, некоторые решения вполне можно портировать туда-обратно.
Oracle в наличии нет, но есть желание решить задачку и есть PostgreSQL:
select
hw.actress, h.h as husband, case when h.h is not null then h.n end as husb_no
from hollywood hw,
rows from (
regexp_split_to_table(husbands, E'\\s*,\\s*'),
nullif(id, null)
) with ordinality h (h, x, n)
order by hw.id, h.n;
Оно же с комментариями:
select
hw.actress,
h.h as husband,
-- Выводятся номера только для тех строк, которые сформированы с помощью regexp_split_to_table
case when h.h is not null then h.n end as husb_no
from hollywood hw,
-- ROWS FROM позволяет комбинировать результаты работы нескольких функций
rows from (
-- Функция разбивает столбец husbands по запятой (попутно удаляя все пробелы по обе стороны запятой),
-- выводя каждый фрагмент отдельной строкой. При этом для строк с husbands is null ничего не выводится.
-- E'...' - строка, "заэскейпленная" в стиле C
regexp_split_to_table(husbands, E'\\s*,\\s*'),
-- Функция используется, чтобы сохранить в выводе строки с husbands is null (ликвидирует дискриминацию Шарлиз)
-- Просто возвращает id, если он не null, что гарантирует наличие в выводе всех строк исходной таблицы
nullif(id, null)
-- Нумерует строки, получаемые из ROWS FROM
-- Нумерация начинается с начала (с 1) для каждой строки исходной таблицы
) with ordinality
-- Задается алиас для результатов конструкции ROWS FROM
h (h, x, n)
order by hw.id, h.n;
Если что, фраза «В запросе было много лишнего» была про мой же первый вариант.
Изначально там вообще было generate_series(1, 10000), поэтому условие g.s <= b.quantity было необходимо. Потом вспомнил про lateral и, следовательно, про возможность использовать b.quantity вместо 10000. Заменил, но то, что условие стало ненужным, заметил слишком поздно.
Кстати, спасибо Alhymik за интересную задачку. Заставила обратить внимание на несколько возможностей SQL, котрые до этого не использовал, а они, оказывается, очень удобны. В частности LATERAL и «CREATE TABLE table_name AS ...».
select b.ingredient, b.measure, 1 as quantity
from bottle b
inner join generate_series(1, b.quantity) g (s)
on g.s <= b.quantity
order by b.id;
Перед generate_series подразумевается lateral, но функция и так может обращаться к полям таблицы, указанной в from ранее.
Еще в Postgres работает способ 4, если после with добавить recursive.
Если после уничтожения виртуальной машины создатётся новая VM с тем же MAC-адресом, разве DHCP-сервер просто не выдаст новой VM тот же самый IP-адрес, что был у уничтоженной VM?
Да, прошу прощения, текст в скобках полностью ускользнул из внимания. И ведь специально еще раз перечитывал условие перед тем, как комментировать. Теперь задаюсь вопросом: «зачем на самом деле нужен мозг», выкидывающий такие фортеля"? :)
Насчёт лёгкости могу и ошибаться — это надо проверять на неподготовленных испытуемых.
Мне кажется, с помощью игральных карт можно добиться более точной аналогии с посетителями бара, если в качестве возраста брать достоинство карты, а рубашки раскрашивать самостоятельно, обозначая цветами алкогольную и безалкогольную выпивку. Чтобы убрать неоднозначность с фосками, можно разделить в условии карты на «числовые» и «картинки». В таком случае анологией условия «мы не продаём алкоголь несовершеннолетним» будет «мы не красим рубашки числовых карт в синий цвет».
В случае же букв и цифр вводятся дополнительные абстракции — нужно сильнее напрягаться, чтобы определить, является буква гласной или согласной, а цифра чётной или нечётной. Плюс к этому сходства между буквами и цифрами намного больше, чем между возрастом посетителя и типом выпивки. Есть вероятность, что все эти нюансы заметно усложняют решение задачи.
Из Postgres на Oracle и из Oracle на Postgres люди мигрируют без особого дискомфорта.
Про направление MySQL -> PG уже не раз говорили выше.
PG -> MySQL… Лично знаю пару человек, которым при переходе на другую работу пришлось пересесть с PG на MySQL. Цитировать высказывания не буду, ибо забанят, но скажу, что оба переживают такую перемену весьма болезненно.
Дошло, что для разворачивания записи прекрасно подойдет та же coalesce — она может принять аргумент любого типа (в данном случае типа record) и вернуть его без изменений, а это именно то, что нужно:
Идея в том, чтобы сначала свернуть запись в один столбец, к которыму можно применить CASE только один раз, а после получения нужной записи развернуть её обратно по столбцам. Функция row_to_json сворачивает запись, функция json_populate_record возвращает значение композитного типа, а если функция, возвращающая композитный тип, вызвана в секции FROM, то postgres разворачивает результат по отдельным столбцам. Но так получается только с функциями. Если обернуть функцию в подзапрос, то есть, вместо json_populate_record(...) написать LATERAL (SELECT json_populate_record(...)), запись не развернётся, а будет выведена как композитный столбец.
Запись можно было свернуть в столбец и без привлечения json, например, «SELECT ROW(election.*) AS e_record FROM election» выведет записи из election в один композитный столбец e_record. То есть, для того, чтобы свернуть запись можно использовать выражение ROW на месте функции row_to_json. Но вот функции, которая просто возвращала бы запись и которую можно было бы вызвать в секции FROM, чтобы запись развернулась, я не нашел.
Не уверен, что изложил понятно. Сам, наверное, не понял бы, если бы не был в теме :)
А с COALESCE да, про NULL в отдельных полях не подумал, надо менять на CASE.
Насчет rowid. В таблицах postgres семь штук системных столбцов, один из которых, oid, как раз аналог rowid, и до версии 8.1 oid в пользовательских таблицах создавался по умолчанию. Но так как это 32-битное целое, в больших и долгоживущих бд он уже не гарантирует уникальность, поэтому с версии 8.1 oid-ы есть только в системных таблицах, а в пользовательских по умолчанию не создаются. Хотя можно при создании таблицы указать WITH OIDS или вообще вернуть oid-ы по умолчанию в настройках сервера.
Но для проверки существования строки можно взять и другой системный столбец, например tableoid:
Только теперь стало громоздко и лично я выбирал бы первый вариант :)
Способы 1 и 2 работают с минимальными изменениями — :bound нужно заменить на конкретное значение, а во втором способе плюс к этому просто удалить «from dual».
Аналог способа 3:
или для удобочитаемости можно вынести виртуальную таблицу d из подзапроса в with:
«values (0, 'Полковник', 'кот', null::integer)» везде можно заменить на аналогичный select, только придется явно указать тип для текстовых полей, зато имена колонкам можно задать на месте через as. Пример с with:
Открыл для себя конструкцию ROWS FROM. Спасибо еще раз :)
Правда, все равно несколько громоздко получилось.
По выборам пока пытаюсь что-нибудь покрасивее соорудить, может уже завтра.
И я все-же больше по postgres, а в таких задачках приходится использовать инструменты, специфичные для конкретной СУБД.
Хотя, некоторые решения вполне можно портировать туда-обратно.
Оно же с комментариями:
Изначально там вообще было generate_series(1, 10000), поэтому условие g.s <= b.quantity было необходимо. Потом вспомнил про lateral и, следовательно, про возможность использовать b.quantity вместо 10000. Заменил, но то, что условие стало ненужным, заметил слишком поздно.
Кстати, спасибо Alhymik за интересную задачку. Заставила обратить внимание на несколько возможностей SQL, котрые до этого не использовал, а они, оказывается, очень удобны. В частности LATERAL и «CREATE TABLE table_name AS ...».
Перед generate_series подразумевается lateral, но функция и так может обращаться к полям таблицы, указанной в from ранее.
Еще в Postgres работает способ 4, если после with добавить recursive.
Мне кажется, с помощью игральных карт можно добиться более точной аналогии с посетителями бара, если в качестве возраста брать достоинство карты, а рубашки раскрашивать самостоятельно, обозначая цветами алкогольную и безалкогольную выпивку. Чтобы убрать неоднозначность с фосками, можно разделить в условии карты на «числовые» и «картинки». В таком случае анологией условия «мы не продаём алкоголь несовершеннолетним» будет «мы не красим рубашки числовых карт в синий цвет».
В случае же букв и цифр вводятся дополнительные абстракции — нужно сильнее напрягаться, чтобы определить, является буква гласной или согласной, а цифра чётной или нечётной. Плюс к этому сходства между буквами и цифрами намного больше, чем между возрастом посетителя и типом выпивки. Есть вероятность, что все эти нюансы заметно усложняют решение задачи.