plpgsql: A RECORD declaration ignores its default expression

Open
#3,386 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
70/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
go, postgresql, sql
Domain
databases

Research direction

Start by running the two SQL reproductions in the issue, covering a plain PL/pgSQL function and a trigger function with a RECORD default. Trace RECORD declaration and block-entry initialization, then verify that the function returns (1,a) and the trigger inserts (1, 'a') without the unassigned-record error.

Written by the indexing model from the issue text.

Description

A RECORD declaration's default expression is silently dropped. The record is left unassigned, so the first reference to it fails with record "<name>" is not assigned yet.

PostgreSQL assigns the default when the block is entered.

Verifying

A plain function:

CREATE FUNCTION f() RETURNS text LANGUAGE plpgsql AS $$
DECLARE r RECORD := ROW(1, 'a');
BEGIN
	RETURN r::text;
END; $$;

SELECT f();

PostgreSQL returns (1,a). Doltgres errors with record "r" is not assigned yet.

A trigger function, which is how this is most likely to be hit:

CREATE TABLE src (id int, note text);
CREATE TABLE res (id int, note text);

CREATE FUNCTION trg() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE whole RECORD := NEW;
BEGIN
	INSERT INTO res VALUES (whole.id, whole.note);
	RETURN NEW;
END; $$;

CREATE TRIGGER t AFTER INSERT ON src FOR EACH ROW EXECUTE FUNCTION trg();

INSERT INTO src VALUES (1, 'a');

PostgreSQL writes (1, 'a') to res. Doltgres fails the INSERT with record "whole" is not assigned yet.

Declaring the same record without a default and assigning it in the body works, so this is specific to the default on the declaration. Reproduced on main at c22eb497.

Dominant language
Go
Stars
2.1k
Forks
75
Avg merge
1d 2h
Merged PRs (30d)
135

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from dolthub/doltgresql

All issues in dolthub/doltgresql

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.