问题[functions](dba)

示例：https ://dbfiddle.uk/bCSwVpd9

数据库：

CREATE TABLE entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TYPE entity_id AS (
  id bigint
);

功能：

CREATE FUNCTION get_entities (
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  entity_ids entity_id DEFAULT NULL
)
RETURNS TABLE (
  id bigint
)
LANGUAGE SQL
AS $BODY$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      -- filter by id list if provided
      entity_ids IS NULL OR id IN (
        SELECT
          id
        FROM
          entity_ids
      )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$BODY$;

关键是我想编写一个分页多选函数，它可以从分页信息和一组 ID 中工作。它上面的函数的问题崩溃了：

ERROR:  relation "entity_ids" does not exist
LINE 22:           entity_ids

对这个问题也有类似的反应：first，second。但是，它们围绕作为标识符字符串的参数，而不是复合记录类型和 use plpgsql，这可能不重要，也可能不重要。

我需要用 0 和 1 组合制作一个矩阵：

SELECT 1 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 1 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,1 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,0 AS F3,1 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 1 AS F5 FROM DUAL

您是否有一些更棘手或更优雅的代码来实现这一目标？

我在 Oracle 19c

谢谢

我试图创建一个接受 uuid 列表作为参数的函数。

我的代码：

create or replace function get_child_groups(input_parents_id uuid[])
returns table(id uuid, name text, parent_id uuid)
as
$$
WITH RECURSIVE c AS (
    SELECT da.*
      from device_group_v2 d
               JOIN device_group_associations da on da.parent_id = d.id
      where d.id in (input_parents_id)

      UNION ALL

      SELECT sa.*
      FROM device_group_associations AS sa JOIN c ON c.child_id = sa.parent_id)
select * from(select d.* from c join device_group_v2 d on c.parent_id = d.id OR c.child_id = d.id) as x
union
select * from device_group_v2 where id in (input_parents_id)
$$ language sql;

这失败了：

[42883] 错误：运算符不存在：uuid = uuid[] 没有运算符与给定的名称和参数类型匹配。您可能需要添加显式类型转换。职位：290

我究竟做错了什么？那里需要一种类型转换吗？

Postgres 12+ 支持生成的列：https ://www.postgresql.org/docs/current/ddl-generated-columns.html

从文档来看，语法似乎是有限的——它强制人们显式地命名生成列所依赖的列。

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

有没有办法将整行传递给生成函数？就像是

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS generator_function(current_row) STORED
);

如果我在 PostgreSQL 中有一个函数，我可以username像这样验证它的参数（例如一个参数）：

IF LENGTH(username) < 4 THEN
    RAISE EXCEPTION 'Usernames must contain at least four characters';
END IF;

我想不通的是如何对 SELECT 查询的结果做同样的事情，即。就像是：

IF LENGTH(SELECT * FROM USERS WHERE username=username) > 1 THEN
    RAISE EXCEPTION 'Username already exists';
END IF;

（是的，我知道 PostgreSQL 会在没有那行的情况下给我一个错误，但我想要一个更友好的错误消息。）

在（Postgre）SQL中是否可能出现类似的情况，如果可以，有人可以指出我可以查看的语法（或它的示例）吗？

我正在重组和统一 PostgreSQL 中的大型数据库。其中一部分是根据它们在“键”中的值将一些大表拆分为较小的表。我为此编写的函数运行良好，如下所示：

CREATE OR REPLACE FUNCTION split_tables (table_string varchar(100)) RETURNS void AS
$$
DECLARE
r_row record;

BEGIN
FOR r_row IN SELECT type FROM type_list
LOOP
EXECUTE 'CREATE TABLE public.id_' || table_string || '__' || r_row.type || ' AS (SELECT * FROM id_'
|| table_string || ' WHERE lower(id_' || table_string || '.key) = lower(''' || r_row.type || '''));';
END LOOP;
END;
$$
LANGUAGE plpsql;

因此，这将遍历 type_list 中的所有条目并为每种类型创建表。要对所有需要拆分的表执行此操作，我将此函数称为 SELECT tobesplit.id FROM tobesplit, LATERAL split_tables(tobseplit.id);

但是，这会为 type_list 中的所有条目创建表，即使表中没有要为某些类型拆分的条目。所以最后，我创建了一大堆我不需要的空表。我寻找了仅在结果不为空时才创建表的方法，并在以下答案中找到了可能的解决方案：CREATE TABLE IF ONLY NOT EMPTY RESULT SET。

如果我单独测试它，这会起作用，但我不知道如何将它合并到执行语句中。一旦我将 IF 或 CASE 子句写入 EXECUTE 语句，我总是会遇到语法错误。

我很感谢任何想法:)

我有一个具有内联函数的查询：

with function with_f(p_text in varchar2) return varchar2 is
  begin
    return p_text;
  end;
select with_f(dummy) from dual

有没有办法在 SQL Developer 中获取该查询的解释计划？

我试过这个：

explain plan for (
with function with_f(p_text in varchar2) return varchar2 is
  begin
    return p_text;
  end;
select with_f(dummy) from dual
); 
select plan_table_output from table(dbms_xplan.display());

但我收到以下错误：

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

我有这个功能：

CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
  _isHoliday BOOLEAN;
BEGIN
  LOOP
    SELECT COUNT(*) > 0 INTO _isHoliday 
    FROM holidays 
    WHERE holiday = DATE(dtDateTime);
    
    IF _isHoliday THEN
      dtDateTime := dtDateTime + INTERVAL '1 DAY';
    ELSE
      EXIT;
    END IF;
  END LOOP;
  RETURN dtDateTime;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER;

如果我运行这个命令：

select laborable_day('2022-01-01 18:53:11.14297-05'::TIMESTAMPTZ);

我得到：

+------------------------------+
| laborable_day                |
|------------------------------|
| 2022-01-02 23:53:11.14297+00 |
+------------------------------+
SELECT 1
Time: 0.012s

为什么时区信息会丢失？

我正在阅读有关CONCAT()函数的信息，我注意到了这一点：

mysql> SELECT CONCAT(14.3);
        -> '14.3'

在 MySQL 8.0 命令行客户端中运行此命令时，输出没有引号。所以我想验证输出的数据类型（我假设它是字符串数据类型）。请帮我找到输出的数据类型。

我正在尝试NVL在 postgres 中使用。

create or replace function nvl (anyelement, anyelement)
returns anyelement language sql as $$
    select coalesce(cast( $1 as decimal), cast( $2 as decimal))
$$;

但是，对于以下示例，这对我来说失败了：

testdb=> select nvl(1,2);
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Actual return type is numeric.
CONTEXT:  SQL function "nvl" during inlining

testdb=> SELECT nvl( sum(balance), 0 ) as b FROM db.bank WHERE user = 123;
ERROR:  function nvl(numeric, integer) does not exist
LINE 1: SELECT nvl( sum(balance), 0 ) as b FROM db.bank...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

当我将其更改为：

create or replace function nvl (anyelement, anyelement)
returns anyelement language sql as $$
    select case when $1 is null then $2 else $1 END 
$$;

第一个例子有效。但我仍然有失败：

testdb=> SELECT nvl( sum(balance), 0 ) as b FROM db.bank WHERE user = 123;
ERROR:  function nvl(numeric, integer) does not exist
LINE 1: SELECT nvl( sum(balance), 0 ) as b FROM db.bank...
       

希望得到一些帮助来解决这个问题。