Динамический поворот PostgreSQL

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

У меня есть этот запрос,

SELECT child_name,
    SUM(CASE WHEN date_string = '2025' THEN _value END) AS "X.2025",
    SUM(CASE WHEN date_string = '2026' THEN _value END) AS "X.2026",
    SUM(CASE WHEN date_string = '2027' THEN _value END) AS "X.2027",
    SUM(CASE WHEN date_string = '2028' THEN _value END) AS "X.2028",
    SUM(CASE WHEN date_string = '2029' THEN _value END) AS "X.2029",
    SUM(CASE WHEN date_string = '2030' THEN _value END) AS "X.2030",
    SUM(CASE WHEN date_string = '2031' THEN _value END) AS "X.2031",
    SUM(CASE WHEN date_string = '2032' THEN _value END) AS "X.2032",
    SUM(CASE WHEN date_string = '2033' THEN _value END) AS "X.2033"
FROM prod."tbl.SystemRegions"
WHERE ("model_name" = model_name)
AND (property_name = 'Load')
GROUP BY child_name
ORDER BY child_name;`

Есть ли способ сделать поворот динамическим, потому что разные «имя_модели» будут иметь разные строки даты начала и окончания?

Я пробовал это на тестовом столе,

CREATE TABLE test_stuff_here."ProductSales" (
    Productname varchar(50),
    year_value text,
    Sales int
);

INSERT INTO test_stuff_here."ProductSales" VALUES
    ('A','2017',100),
    ('A','2018',150),
    ('A','2019',300),
    ('A','2020',500),
    ('A','2021',450),
    ('A','2022',675),
    ('B','2018',900),
    ('B','2019',1120),
    ('B','2020',750),
    ('B','2021',1500),
    ('B','2022',1980);

    CREATE OR REPLACE FUNCTION test_stuff_here.pivot_dynamic(IN table_name text) RETURNS void AS $$
    DECLARE
    col_names TEXT;
    dynamic_sql TEXT;
    BEGIN
    SELECT STRING_AGG(DISTINCT year_value, ', ') INTO col_names
    FROM test_stuff_here."ProductSales";

        dynamic_sql := format('
        SELECT productname, ' || col_names || '
        FROM prod.crosstab(
             SELECT productname, year_value, sales FROM test_stuff_here.%I,

             ) AS ct (productname varchar, ' || col_names || ' text)', table_name
         );

     EXECUTE dynamic_sql;

END;
$$ LANGUAGE plpgsql;

И я получаю эту ошибку

ERROR:  syntax error at or near "SELECT"
LINE 4: SELECT productname, year_value, sales FROM test\_...
        ^
QUERY:  
SELECT productname, 2017, 2018, 2019, 2020, 2021, 2022
FROM prod.crosstab(
SELECT productname, year_value, sales FROM test_stuff_here."ProductSales",

    ) AS ct (productname varchar, 2017, 2018, 2019, 2020, 2021, 2022 text)

CONTEXT:  PL/pgSQL function test_stuff_here.pivot_dynamic(text) line 17 at EXECUTE

SQL state: 42601

Нет, потому что при вызовах функций SQL во время вызова должны быть известны имена и типы выходных данных. Динамически выполнять поворот проще в вашем приложении, а не в базе данных, иначе вам придется использовать двухэтапный вызов, при котором первый вызов запускает операцию и куда-то сбрасывает динамическую структуру, а второй шаг ее считывает. Таким образом, первый имеет постоянную структуру возврата, а сводные данные — это всего лишь побочный эффект. И эта динамическая структура уже установлена и известна во время второго призыва.

— 21.06.2024 08:52

Похожие темы: 1 , 2 , 3 , 4 , 5.

— 21.06.2024 09:20

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

— 21.06.2024 09:27

sql postgresql

21.06.2024 05:13

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

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

Есть ли способ сделать поворот динамическим, потому что разные «имя_модели» будут иметь разные строки даты начала и окончания?

Нет, не напрямую. Выходные столбцы, типы и имена должны быть известны во время вызова, и в вашем случае имена должны меняться, и их количество может меняться.

Чтобы обойти ограничение, вам понадобится двухэтапный процесс:

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

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

демо на db<>fiddle

CREATE OR REPLACE FUNCTION test_stuff_here.pivot_dynamic(
   IN input_table_schema text
  ,IN input_table_name text
  ,IN output_table_schema text default 'test_stuff_here'
  ,IN output_table_name text default 'pivot_dynamic_out') 
RETURNS void AS $f$
DECLARE col_names TEXT;
        col_names_typed TEXT;
BEGIN
  EXECUTE format(
     $g$SELECT STRING_AGG(DISTINCT '"'||year_value||'"', ', ') 
              ,STRING_AGG(DISTINCT '"'||year_value||'" int', ', ')
        FROM %1$I.%2$I
     $g$, input_table_schema
        , input_table_name)
  INTO  col_names
      , col_names_typed;
  EXECUTE format(
     $g$DROP TABLE IF EXISTS %1$I.%2$I;
        CREATE TABLE IF NOT EXISTS %1$I.%2$I AS
        SELECT productname, %3$s
        FROM crosstab(
           $h$SELECT productname, year_value, sales FROM %4$I.%5$I
           $h$) AS ct (productname varchar, %6$s)
     $g$, output_table_schema
        , output_table_name
        , col_names
        , input_table_schema
        , input_table_name
        , col_names_typed);
END $f$ LANGUAGE plpgsql;

Два шага:

select test_stuff_here.pivot_dynamic('test_stuff_here','ProductSales');
select * from test_stuff_here.pivot_dynamic_out;

наименование товара 2017 год 2018 год 2019 год 2020 год 2021 год 2022 год А 100 150 300 500 450 675 Б 900 1120 750 1500 1980 год нулевой

Согласно комментарию, crosstab() ожидает аргумент text с запросом в нем, и вы не заключили запрос в кавычки, сделав его подзапросом с несколькими столбцами.
В запросе, который вы задали crosstab(), по какой-то причине была запятая.
col_names нельзя повторно использовать в AS после вызова crosstab(), поскольку после имени каждого столбца отсутствует спецификация типа.
Вам будет удобнее использовать именованные долларовые кавычки вместо одинарных кавычек, если вы вкладываете строки в кавычки. Тело вашей функции уже представляет собой один слой цитирования, затем format() имеет свой собственный, а затем внутри этого format() вызов crosstab() также требует цитируемого text значения.