PostgreSQL: получение нескольких значений из типа записи

Я пишу программу sql, которая создает таблицу со столбцами, вычисляемыми в функции. Функция возвращает тип записи. Вот как выглядит заголовок:

create or replace function get_items(col1 int, col2 int) returns record

То, что я хотел бы сделать, это:

create table table_items as (
with q as (
    select *,
   (SELECT * FROM get_items(t.col1, t.col2) AS (item1 integer, item2 integer))
    from 
    table_t as t
    )
    select * from q
);

однако это приводит к:

ERROR:  subquery must return only one column

Чтобы исправить ошибку, я изменил код на:

create table table_items as (
with q as (
    select *,
   (SELECT item1 FROM get_items(t.col1, t.col2) AS (item1 integer, item2 integer)),
   (SELECT item2 FROM get_items(t.col1, t.col2) AS (item1 integer, item2 integer)) 
    from 
    table_t as t
    )
    select * from q
);

Это решение работает, но в два раза медленнее, когда в дополнение к item1 получается item2. Я предполагаю, что это потому, что один и тот же запрос выполняется дважды. Есть ли способ получить оба элемента, вызывая функцию только один раз? Большое спасибо!

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
59
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Это хороший пример использования бокового соединения.

create table table_items as 
select *
from table_t as t
cross join lateral
get_items(t.col1, t.col2) as l(item1 integer, item2 integer);

Кажется, это то, что мне было нужно. Большое спасибо!

Marina 21.12.2022 13:59

Я предполагаю, что причина в том, что returns record полиморфен. Попробуйте с RETURNS TABLE (col1 int, col2 int) или верните пользовательский тип

Прямая реализация того, что вы планировали изначально, должна быть быстрее, чем боковое соединение. Как предложил @esmin : если вы получаете только (item1 integer, item2 integer) тип записи из функции, лучше определить его заранее ( вот онлайн-демонстрация для всего ниже):

drop function if exists get_items;

create type get_items_return_rec as (a int, b int);

create or replace function get_items(col1 int,col2 int) 
  returns get_items_return_rec language plpgsql as $$
begin
    return (col1,col2)::get_items_return_rec;
end $$;

Что позволит вам обращаться к его полям напрямую, именно так, как вы хотели - вызывая его только один раз, а затем разбивая на поля:

explain analyze 
select a,
       b,
       (rec).a,
       (rec).b 
from (
  select a,
         b,
         get_items(a,b) as rec
  from test) subquery;
--                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------
-- Seq Scan on test  (cost=0.00..360098.00 rows=700000 width=16) (actual time=2.244..816.545 rows=700000 loops=1)
-- Planning Time: 0.061 ms
-- JIT:
--   Functions: 2
--   Options: Inlining false, Optimization false, Expressions true, Deforming true
--   Timing: Generation 0.451 ms, Inlining 0.000 ms, Optimization 0.193 ms, Emission 1.807 ms, Total 2.451 ms
-- Execution Time: 841.719 ms

Сравните это с боковым соединением и вашим первоначальным решением на том же примере из 700 тыс. строк:

explain analyze
  select a,
         b,
         (select c from get_items(a,b) as rec(c int,d int)),
         (select d from get_items(a,b) as rec(c int,d int))
  from test;
--                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
-- Seq Scan on test  (cost=0.00..374098.00 rows=700000 width=16) (actual time=6.845..2275.461 rows=700000 loops=1)
--   SubPlan 1
--     ->  Function Scan on get_items rec  (cost=0.25..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=700000)
--   SubPlan 2
--     ->  Function Scan on get_items rec_1  (cost=0.25..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=700000)
-- Planning Time: 0.073 ms
-- JIT:
--   Functions: 18
--   Options: Inlining false, Optimization false, Expressions true, Deforming true
--   Timing: Generation 1.311 ms, Inlining 0.000 ms, Optimization 0.319 ms, Emission 6.256 ms, Total 7.886 ms
-- Execution Time: 2300.981 ms
explain analyze
  select a,
         b
  from  test
  cross join lateral
  get_items(a,b) as rec(c int,d int);

--                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
-- Nested Loop  (cost=0.25..24098.25 rows=700000 width=8) (actual time=0.064..1116.634 rows=700000 loops=1)
--   ->  Seq Scan on test  (cost=0.00..10098.00 rows=700000 width=8) (actual time=0.014..44.813 rows=700000 loops=1)
--   ->  Function Scan on get_items rec  (cost=0.25..0.26 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=700000)
-- Planning Time: 0.080 ms
-- Execution Time: 1140.850 ms

Если вам нужна скорость, посмотрите, можете ли вы переписать свою функцию как простую функцию SQL вместо использования plpgsql. Пример тривиален, но он все же иллюстрирует сравнительную разницу, которую вы можете ожидать:

drop function if exists get_items;
create or replace function get_items(col1 int,col2 int) 
  returns get_items_return_rec language sql stable as $$
select (col1,col2)::get_items_return_rec
$$;

explain analyze
select a,
       b,
       (rec).a,
       (rec).b
from (
  select a,
         b,
         get_items(a,b) as rec
  from test) subq;
--                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------
-- Seq Scan on test  (cost=0.00..10098.00 rows=700000 width=16) (actual time=0.010..64.552 rows=700000 loops=1)
-- Planning Time: 0.085 ms
-- Execution Time: 88.892 ms

Кроме того, проверьте уровни волатильности функций и, если ваша функция не вызывает побочных эффектов, сделайте их как минимум stable.

Большое спасибо за ваш ответ. Я сравнил два решения, оказалось, что решение с использованием перекрестного соединения позже было примерно в 4 раза быстрее на срезе набора данных из 1000 строк.

Marina 21.12.2022 17:10

@Marina Они не исключают друг друга, так что вы можете их комбинировать. Вы пытались переключить функцию на обычный sql?

Zegarek 21.12.2022 19:04

Я объединил их по приведенному вами примеру (спасибо!). Время выполнения программы сравнимо с тем, что использует тип записи. Я попытался изменить plpgsql на sql, но он выдает синтаксическую ошибку при объявлении переменной. Мне потребуется некоторое время, чтобы понять, как это сделать. Еще раз спасибо за помощь.

Marina 23.12.2022 14:33

Другие вопросы по теме