在Postgres 13.4中的SELECT查询中从CTE调用insert *函数*

Question

我正在编写通过pg_cron运行的实用程序代码，有时希望这些例程将一些结果插入到dba.event_log的自定义表中。我有一个基本的日志表作为起点：

DROP TABLE IF EXISTS dba.event_log;

CREATE TABLE IF NOT EXISTS dba.event_log (
    dts      timestamp    NOT NULL DEFAULT now(),
    name     citext       NOT NULL DEFAULT '',
    details  citext       NOT NULL DEFAULT '');

下面的玩具示例执行一个select操作，然后使用该值作为外部查询的结果，并将其作为insert的values元素进入event_log。

WITH

values_cte AS (
 select clock_timestamp() as ct
),

log as(
insert into event_log (
               name,
               details)

     values (
             'CTE INSERT check',
             'clock = ' || (select ct::text from values_cte)
             )
)

select * from values_cte;

select * from event_log;

每次运行这个程序时，我都会得到一个新的日志条目，其中包含了clock_timestamp()，这样就可以很容易地看到正在发生的事情：

+----------------------------+------------------+---------------------------------------+
| dts                        | name             | details                               |
+----------------------------+------------------+---------------------------------------+
| 2021-11-10 11:58:43.919151 | CTE INSERT check | clock = 2021-11-10 11:58:43.919821+11 |
| 2021-11-10 11:58:56.769512 | CTE INSERT check | clock = 2021-11-10 11:58:56.769903+11 |
| 2021-11-10 11:58:59.632619 | CTE INSERT check | clock = 2021-11-10 11:58:59.632822+11 |
| 2021-11-10 12:00:50.442282 | CTE INSERT check | clock = 2021-11-10 12:00:50.442646+11 |
+----------------------------+------------------+---------------------------------------+

稍后我可能会充实这个表，现在我会将日志inserts变成一个简单的调用。下面是一个简单的insert函数：

DROP FUNCTION IF EXISTS dba.event_log_add(citext,citext);

CREATE FUNCTION dba.event_log_add(
   name_in        citext,
   description_in citext)

RETURNS int4

LANGUAGE sql AS

$BODY$

insert into event_log (name, details)
     values (name_in, description_in)
   returning 1;

$BODY$;

它认为我应该能够重写原始查询来调用函数，如下所示：

WITH

values_cte AS (
 select clock_timestamp() as ct
),

log as (
select * from dba.event_log_add(   
             'CTE event_log_add check',
             'clock = ' || (select ct::text from values_cte)
             )               
)

select * from values_cte;

这里唯一的区别是，VALUES现在作为参数传递给dba.event_log_add，而不是直接在查询中在INSERT中使用。我知道这个错误：

ERROR:  function dba.event_log_add(unknown, text) does not exist
LINE 8: select * from dba.event_log_add(   
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. 0.000 seconds. (Line 1).

我试过了

显式casts

• Rewriting函数作为存储过程，并使用CALL

• Rewriting在PL/PgSQL中使用函数，返回VOID并运行PERFORM.

似乎什么都没起作用。我检查了search_path，使用了限定名，检查了权限等。有些方法抛出似乎不适用的错误，比如上面的方法，其他方法没有抛出错误，也没有插入任何数据。直接运行，该函数工作良好，它只在CTE内爆炸。

我想我遗漏了一些关于使用函数而不是直接INSERT的东西。有什么好办法吗？在查看了文档，并在这里寻找更多信息后，我对规则有了更清楚的了解。但不完全是。如果我正确地阅读了它，数据修改的CTE就会被外部查询所控制/调节。我绝对没有把握其中的微妙之处。我是否以某种方式将上下文更改为将INSERT移动到函数中，从而使查询和CTE中的代码得到解释？

https://www.postgresql.org/docs/13/queries-with.html#QUERIES-WITH-MODIFYING

Answer 1

您的函数需要citext类型的参数，但传递的是text类型的值。您需要转换参数：

WITH values_cte AS (
 select clock_timestamp() as ct
),log as (
  select event_log_add('CTE event_log_add check'::citext,
                       ('clock = ' || (select ct::text from values_cte))::citext)               
)
select * 
from log;

将参数定义为text可能更容易一些，在插入过程中，转换过程将自动完成：

CREATE FUNCTION event_log_add(
   name_in        text,
   description_in text)
RETURNS int4
LANGUAGE sql AS
$BODY$
  insert into event_log (name, details)
  values (name_in, description_in)
  returning 1;
$BODY$;

WITH values_cte AS (
 select clock_timestamp() as ct
),log as (
  select event_log_add('CTE event_log_add check',
                       'clock = ' || (select ct::text from values_cte))   
)
select * 
from log;

如果需要，可以在函数中添加显式强制转换。