Calling an insert *function* from a CTE in a SELECT query in Postgres 13.4-CodePudding

I'm writing up utility code to run through pg_cron, and sometimes want the routines to insert some results into a custom table at dba.event_log. I've got a basic log table as a starting point:

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 '');

The toy example below performs a select operation, and then uses that value as the result of the outer query, and as a values element of an insert into the 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;

Every time I run this, I get a new log entry, with the clock_timestamp() to make it easy to see that something is happening:

 ---------------------------- ------------------ --------------------------------------- 
| 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 |
 ---------------------------- ------------------ ---------------------------------------

I'll likely enrich the table later, and I'd to make the log inserts into a simple call now. Below is a simple insert function:

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$;

It sees like I should be able to rewrite the original query to call the function, like this:

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;

The only difference here is that the VALUES are now passed as parameters to dba.event_log_add, rather than used in an INSERT directly in the query. I get this error:

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).

I've tried

Explicit casts
Rewriting the function as a stored procedure and using CALL
Rewriting the function in PL/PgSQL, returning VOID, and running PERFORM.

Nothing seemed to work. I've checked the search_path, used qualified names, checked permissions, etc. Some approaches throw errors that don't seem to apply, like the one above, others throw no error, and insert no data. Run directly, the function works fine, it only blows up within the CTE.

I think I'm missing something about using a function instead of a direct INSERT. Is there a good way to do this? After looking at the docs, and hunting around here for more information, I'm a bit clearer on the rules. But not entirely. If I'm reading it right, a data-modifying CTE is ruled/regulated by the outer query. There are definitely subtleties that I'm not grasping. Am I changing the context in some way to moving the INSERT into a function, making how the code in the query and CTE are interpreted?

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

CodePudding user response：

Your function expects parameters of type citext but you are passing values of type text. You need to cast the parameters:

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;

It's probably easier to define the parameters as text, during the INSERT the casting will then be done automatically:

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;

If you want, you can add an explicit cast inside the function.