Как отключить таблицу в PostgreSQL

У меня возникают трудности при написании функции Postgres, так как я не знаком с ней. У меня есть несколько таблиц для импорта в Postgres с этим форматом:

id | 1960 | 1961 | 1962 | 1963 | ...
____________________________________
 1    23     45     87     99
 2    12     31    ...

который мне нужно конвертировать в этот формат:

id | year | value
_________________
 1   1960    23
 1   1961    45
 1   1962    87
 ...
 2   1960    12
 2   1961    31
 ...

Я хотел бы представить функцию, чтобы читать так:

SELECT all-years FROM imported_table;
CREATE a new_table;
FROM min-year TO max-year LOOP
     EXECUTE "INSERT INTO new_table (id, year, value) VALUES (id, year, value)";
END LOOP;

Тем не менее, у меня есть реальные проблемы с написанием мельчайших деталей для этого. Мне было бы проще сделать это в PHP, но я убежден, что это проще сделать прямо в Postgres-функции.

Годы (начало и конец) варьируются от таблицы к таблице. И иногда у меня могут быть годы только на каждый пятый год или около того...

Источник

5 ответов

Решение

Полностью динамическая версия требует динамического SQL. Используйте функцию plpgsql с EXECUTE:

Для Postgres 9.2 или старше (до LATERAL было реализовано):

CREATE OR REPLACE FUNCTION f_unpivot_years92(_tbl regclass, VARIADIC _years int[])
  RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
   SELECT id
        , unnest($1) AS year
        , unnest(ARRAY["'|| array_to_string(_years, '","') || '"]) AS val
   FROM   ' || _tbl || '
   ORDER  BY 1, 2'
   USING _years;
END
$func$  LANGUAGE plpgsql;

Для Postgres 9.3 или новее (с LATERAL):

CREATE OR REPLACE FUNCTION f_unpivot_years(_tbl regclass, VARIADIC _years int[])
  RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE (SELECT
     'SELECT t.id, u.year, u.val
      FROM  ' || _tbl || ' t
      LEFT   JOIN LATERAL (
         VALUES ' || string_agg(format('(%s, t.%I)', y, y), ', ')
     || ') u(year, val) ON true
      ORDER  BY 1, 2'
      FROM   unnest(_years) y
      );
END
$func$  LANGUAGE plpgsql;

Около VARIADIC:

Призыв на произвольные годы:

SELECT * FROM f_unpivot_years('tbl', 1961, 1964, 1963);

То же самое, передавая фактический массив:

SELECT * FROM f_unpivot_years('tbl', VARIADIC '{1960,1961,1962,1963}'::int[]);

Для длинного списка последовательных лет:

SELECT * 
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2014)));

Для длинного списка с регулярными интервалами (например, каждые 5 лет):

SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2010,5)));

Вывод по запросу.

Функция принимает:
1. Допустимое имя таблицы - двойные кавычки, если в противном случае это незаконно (например, '"CaMeL"'). Использование типа идентификатора объекта regclass отстаивать правильность и защищаться от внедрения SQL. Вы можете захотеть, чтобы имя сказки квалифицировалось как схема (например, 'public."CaMeL"'). Больше:

2. Любой список чисел, соответствующих (двойным кавычкам) именам столбцов.
Или фактический массив с префиксом с ключевым словом VARIADIC,

Массив столбцов не должен сортироваться каким-либо образом, но таблица и столбцы должны существовать, или возникает исключение.

Выход отсортирован по id а также year (как integer). Если вы хотите, чтобы годы сортировались в соответствии с порядком сортировки входного массива, сделайте это просто ORDER BY 1, Порядок сортировки по массиву строго не гарантируется, но работает в текущей реализации. Подробнее об этом:

Также работает для NULL ценности.

SQL Fiddle для обоих с примерами.

Рекомендации:

Источник

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

SELECT id, (k).key as year, (k).value as value FROM
  (SELECT j->>'id' as id, json_each_text(j) as k
    FROM (
       SELECT row_to_json(tbl) as j FROM tbl) 
    as q)
    as r
WHERE (k).key <> 'id';

http://sqlfiddle.com/

Источник

Параллельное удаление может быть проще

select
    id,
    unnest(array[1960, 1961, 1962]) as year,
    unnest(array["1960", "1961", "1962"]) as value
from (values
    (1,23,45,87), (2,12,31,53)
) s(id, "1960", "1961", "1962")
;
 id | year | value 
----+------+-------
  1 | 1960 |    23
  1 | 1961 |    45
  1 | 1962 |    87
  2 | 1960 |    12
  2 | 1961 |    31
  2 | 1962 |    53
Источник

Вот способ, основанный на методе https://blog.sql-workbench.eu/post/dynamic-unpivot/ и другом ответе на этот вопрос , но использующем расширение hstore

      SELECT
  id,
  r.key AS year,
  r.value AS value
FROM
  imported_table t
CROSS JOIN
  each(hstore(t.*)) AS r(key, value)
WHERE
  -- This chooses columns that look like years
  -- In other cases you might need a different condition
  r.key ~ '^[0-9]{4}$'

У него есть несколько преимуществ перед другими решениями:

  • Мы надеемся, что использование hstore, а не jsonb, сводит к минимуму проблемы с преобразованием типов (хотя hstore преобразует все в текст).
  • Столбцы не обязательно должны быть жестко запрограммированы или известны заранее. Здесь столбцы выбираются регулярным выражением по имени, но вы можете использовать любую логику SQL на основе имени (или даже значения)
  • Это не требует PL/pgSQL - это все SQL
Источник

Самый простой способ это union all:

select id, 1960 as year, "1960" as value
from table t
union all
select id, '1960', "1961"
from table t
. . .;

Несколько более изощренный способ:

select t.id, s.yr,
       (case when s.yr = 1960 then "1960"
             when s.yr = 1961 then "1961"
             . . .
        end) as value
from table t cross join
     generate_series(1960, 1980) s(yr);

Вы можете поместить это в другую таблицу, используя insert или же create table as,

Источник
Другие вопросы по тегам