citusdata / citusdata/citus

non-IMMUTABLE functions are not allowed in the RETURNING clause

Open
#4,161 1 comment 0 reactions 0 assignees View on GitHub
application-development
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.