The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.
melkij=> VALUES ('кофты'), ('ботинки'), ('пальто');
column1
---------
кофты
ботинки
пальто
(3 строки)
begin;
set local statement_timeout to '100ms';
alter table history add constraint partition_key check (clock < :N) not valid;
commit;
alter table history validate constraint partition_key;
begin;
set local statement_timeout to '100ms';
alter table history rename to history_old_data;
create table history (...) partition by range (clock);
alter table history attach partition history_old_data for values from (minvalue) to (:N);
create table history_part_... partition of history for values from (:N) to (...);
commit;create extension pg_trgm;
create index concurrently db_url_trgm_idx on db1 using gin ("Url" gin_trgm_ops);select EXTRACT(YEAR FROM time) AS year,
sum(cost) filter(where EXTRACT('month' FROM time) = 1) as jan,
sum(cost) filter(where EXTRACT('month' FROM time) = 2) as feb,
...
sum(cost) filter(where EXTRACT('month' FROM time) = 12) as dec
from tablename
where ...
group by 1
order by 1melkij=> create or replace function myfunc()
returns setof record
as $$
declare
res record;
begin
execute 'select 1' into res;
return next res;
execute 'select 2' into res;
return next res;
return;
end;
$$
language plpgsql;
CREATE FUNCTION
melkij=> select * from myfunc() as s(i int);
i
---
1
2
(2 строки)
melkij=> select myfunc();
ERROR: materialize mode required, but it is not allowed in this context
КОНТЕКСТ: PL/pgSQL function myfunc() line 6 at RETURN NEXTmelkij=> \df to_tsvector
Список функций
Схема | Имя | Тип данных результата | Типы данных аргументов | Тип
------------+-------------+-----------------------+------------------------+-------
pg_catalog | to_tsvector | tsvector | json | функ.
pg_catalog | to_tsvector | tsvector | jsonb | функ.
pg_catalog | to_tsvector | tsvector | regconfig, json | функ.
pg_catalog | to_tsvector | tsvector | regconfig, jsonb | функ.
pg_catalog | to_tsvector | tsvector | regconfig, text | функ.
pg_catalog | to_tsvector | tsvector | text | функ.
(6 строк)melkij=> select 'hello'::xml;
xml
-------
hello
(1 строка)
melkij=> select '<foo>hello'::xml;
ERROR: invalid XML content
СТРОКА 1: select '<foo>hello'::xml;
^
ПОДРОБНОСТИ: line 1: Premature end of data in tag foo line 1
<foo>hello
^
line 1: chunk is not well balanced
<foo>hellocreate table part (i int) partition by hash (i);
create table part_0 partition of part FOR VALUES WITH (MODULUS 2, REMAINDER 0);
create table part_1 partition of part FOR VALUES WITH (MODULUS 2, REMAINDER 1);
insert into part select generate_series(1,10);
select * from only part_0;
select * from only part_1;