John Frazer's questions

我正在寻找一种方法来消除 PostgreSQL 数组中的重复项，同时保留其元素的顺序。我目前拥有的是以下功能：

create function array_unique( anyarray ) 
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) ); $$;

create function array_unique_sorted( anyarray ) 
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) order by 1 ); $$;

/* ### TAINT there ought to be a simpler, declarative solution */
create function array_unique_stable( text[] )
  returns text[] immutable strict parallel safe language plpgsql as $$
  declare
    R         text[] = '{}';
    ¶element  text;
  begin
    foreach ¶element in array $1 loop
      if not array[ ¶element ] && R then
        R :=  R || array[ ¶element ];
        end if;
      end loop;
    return R; end; $$;

在上面，array_unique接受任何类型的数组并返回一个删除所有重复项的副本；它们的相对顺序是任意的。 array_unique_sorted是 like array_unique，但元素是相对于彼此排序的；这有时很有用，因为具有相同一组不同元素的所有数组在被此函数标准化后将比较相等。

array_unique_stable已经做了我正在寻找的：给定一个数组（在这个例子中必须是一个text[]数组），它从左到右扫描元素；每当遇到以前看不见的元素时，它就会将该元素添加到结果中。因此，仅保留每个值的第一次出现。

但是，该实现有一些缺点：首先，似乎没有办法编写它，因此它接受了伪类型anyarray。

其次，虽然前两个函数是用 SQL 编写的，但它们可能是内联的， array_unique_stable是用 PL/pgSQL 编写的，因此不能内联。

第三，我无法在纯 SQL 中提出解决方案，这让我很困扰……

我有下表，其中的数据如下所示；linenrs 单调递增但不一定连续；当该key字段包含一个省略号...，表示从上面继续的条目时：

create table source (
  linenr    integer unique not null,
  key       text not null,
  value     text );

insert into source values
  (  2, 'tags',  'a'          ),
  (  3, '...',   'b'          ),
  (  4, 'title', 'The Title'  ),
  (  5, 'note',  'this is'    ),
  (  6, '...',   'an EXAMPLE' ),
  (  8, 'title', 'over'       ),
  (  9, '...',   'three'      ),
  ( 10, '...',   'lines'      ),
  ( 11, 'about', 'grouping'   );

现在我想要一个视图，其中根据key字段的内容分配组号；组编号不必是连续的，但对于以除 以外的键开头...并继续通过 is 的所有行key的每组行应该是不同的...，如下所示：

╔════════╤═══════╤═══════╤════════════╗
║ linenr │ group │  key  │   value    ║
╠════════╪═══════╪═══════╪════════════╣
║      2 │     1 │ tags  │ a          ║
║      3 │     1 │ ...   │ b          ║
║      4 │     2 │ title │ The Title  ║
║      5 │     3 │ note  │ this is    ║
║      6 │     3 │ ...   │ an EXAMPLE ║
║      8 │     4 │ title │ over       ║
║      9 │     4 │ ...   │ three      ║
║     10 │     4 │ ...   │ lines      ║
║     11 │     5 │ about │ grouping   ║
╚════════╧═══════╧═══════╧════════════╝

我尝试用窗口/分区和 tabibitosan 模式来做这件事，但还没有想出任何有效的方法；此外，在前一行使用 withlag()对多行连续行 with 没有帮助...。在电子表格中，这是一件非常容易做到的事情，但在 SQL 中，我似乎无法引用当前查询的前一行，可以吗？

我刚刚发现我可以将任何类型的值插入到类型为 PostgreSQL (9.6) 的列中text：

drop table if exists d cascade;
create table d ( a text );
insert into d values ( 42 );
insert into d values ( true );
select a, pg_typeof( a ) from d;

  a   | pg_typeof
------+-----------
 42   | text
 true | text
(2 rows)

这是故意的功能吗？我做错了什么吗？有没有设置可以避免这种情况？这不违反 RDBMS 应该是类型安全的假设吗？

我知道这text在 PostgreSQL 中就像一个包罗万象的东西，这通常很方便，因为你可以编写任意类型的字符串表示。但有时你肯定想确保只有字符串被插入到给定的列中，以排除隐式转换值。

我能做些什么来避免“随意”的类型转换吗？

据我所知，目前在 PostgreSQL v9.5（及更高版本？）中不可能获得自定义范围类型的隐式转换。为了说明，当我定义以下演示设置时：

drop schema if exists tac cascade;
create schema tac;

create domain tac.usascii_small_letters
  as text
  check ( value ~ '^[a-z]+$' );

create domain tac.ucid as integer
  check ( value between x'0'::integer and x'10ffff'::integer );

create function tac.ucid_rng_diff( cid_1 tac.ucid, date_2 tac.ucid )
  returns double precision
  language sql
  immutable
  as $$
    select cast( cid_1 - date_2 as double precision );
  $$;

-- A ----------------------------------------------------------------
create type tac.ucid_rng as range (
  subtype       = tac.ucid,
  subtype_diff  = tac.ucid_rng_diff
  );
-- ----------------------------------------------------------------

-- -- B ----------------------------------------------------------------
-- set role dba; -- !!!!!!!!!!!!!!!!!!!!
-- create type tac.ucid_rng;

-- create function tac.ucid_rng_canonical( x tac.ucid_rng )
--   returns tac.ucid_rng
--   language plpgsql
--   as $$
--     begin
--         if not lower_inc(x) then
--             x := tac.ucid_rng(lower(x) + 1, upper(x), '[]');
--         end if;
--         if not upper_inc(x) then
--             x := tac.ucid_rng(lower(x), upper(x) - 1, '[]');
--         end if;
--         return x;
--     end;
--   $$;

-- create type tac.ucid_rng as range (
--   subtype       = tac.ucid
--   , subtype_diff  = tac.ucid_rng_diff
--   , canonical     = tac.ucid_rng_canonical
--   );

-- reset role; -- !!!!!!!!!!!!!!!!!!!!
-- -- ----------------------------------------------------------------

create table tac.words (
  word                    tac.usascii_small_letters,
  cid                     tac.ucid,
  cid_range               tac.ucid_rng
  );

insert into tac.words values
  ( 'foo', 1, '[11,21]' ),
  ( 'bar', 2, '[12,22]' ),
  ( 'zip', 3, '[13,23]' ),
  ( 'dat', 4, '[14,24]' ),
  ( 'baz', 5, '[15,25]' );

select * from tac.words where word between 'a' and 'c';
select * from tac.words where word between 'a'::tac.usascii_small_letters and 'c';
select * from tac.words where cid between 3 and 5;
select * from tac.words where cid_range @> '[17,23]';
select * from tac.words where cid_range @> '[17,23]'::tac.ucid_rng;
select * from tac.words where cid_range @> 23::tac.ucid;
select * from tac.words where cid_range @> 23;

并像使用块 A 但没有块 B 一样运行它，我得到

ERROR:  operator does not exist: integer <@ tac.ucid_rng
LINE 1: select * from tac.words where cid_range @> 23;
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

测试表明，很多使用自定义域/类型的东西都适用于隐式转换。事实上，我可以对我的自定义字符串数据类型进行范围检查，甚至cid_range @> '[17,23]'可以工作。只是神秘地失败了。 cid_range @> 23

现在我尝试为我的tac.cid_rng类型实现一个合适的规范化函数；然而，正如https://stackoverflow.com/a/29939205中所说，那是行不通的：

NOTICE:  argument type tac.ucid_rng is only a shell
NOTICE:  return type tac.ucid_rng is only a shell
ERROR:  PL/pgSQL functions cannot return type tac.ucid_rng

因此，在 PostgreSQL 9.5 中似乎仍然不可能在点包含查询中使用隐​​式转换的自定义范围类型，尽管类型本身是在一组连续整数上非常透明地定义的，并且类似的查询无需任何额外编码即可正常工作。

我想知道我是否在这里遗漏了什么，以及有什么推荐的方法来处理这种情况。我想我可以接受显式强制转换，或者编写一个函数来为我做这件事。

我不明白的是为什么cid_range @> '[17,23]'有效但cid_range @> 23失败了——毕竟，在这两种情况下都必须执行从通用/暗示文字到实际数据类型的转换。从测试中可以看出，从整数文字到tac.ucid域的隐式转换也可以工作，因此很难看出究竟缺少什么需要一段 C 样板代码，它除了通用检查和整数递增/递减外什么都不做。