non-IMMUTABLE functions are not allowed in the RETURNING clause
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Citus currently support immutable functions on RETURNING clause, and the error message is clear about. Opening this issue to track the know limitation.
```SQL
CREATE TABLE todos (id bigserial PRIMARY KEY,
title text,
is_completed boolean,
is_public boolean,
created_at timestamp DEFAULT now(),
user_id text);
SELECT create_distributed_table('todos', 'id');
INSERT INTO todos (title) VALUES ('test') RETURNING id, now();
ERROR: non-IMMUTABLE functions are not allowed in the RETURNING clause
CREATE OR REPLACE FUNCTION throw_error(msg text) RETURNS bool AS
$$
BEGIN
RAISE check_violation USING message=msg;
END;
$$ LANGUAGE plpgsql;
-- by default, the functions are volatile
select provolatile from pg_proc WHERE proname = 'throw_error';
-[ RECORD 1 ]--
provolatile | v
INSERT INTO todos (title) VALUES ('test') RETURNING id, CASE
WHEN 'true' THEN NULL
ELSE throw_error(title)
END;
ERROR: non-IMMUTABLE functions are not allowed in the RETURNING clause
```
Contributor guide
Assessment
This issue has not been assessed yet.