Skip to content

Incorrect substitution with field names declared in RETURNS TABLE (...) #2354

@fulghum

Description

@fulghum

A set-returning user-defined function can use the RETURNS TABLE (...) syntax to return an anonymous composite type, made up of the fields specified in the parentheses. The PL/pgSQL parser we use detects these fields as parameters, which seems to match how Postgres handles them (i.e. you can assign to them and then when you RETURN those values are used in the returned composite value).

Doltgres' parameter substitution logic has trouble when these same field names appear in the query, for example when a column named id is referenced and one of the fields in the anonymous composite is also named id.

Repro:

CREATE TABLE customers (
	id INT PRIMARY KEY,
	name TEXT
);

INSERT INTO customers VALUES (1, 'John'), (2, 'Jane');

CREATE FUNCTION func2(n INT) RETURNS TABLE (id INT, name TEXT) 
LANGUAGE plpgsql
AS $$
BEGIN
	RETURN QUERY
	SELECT c.id, c.name 
	FROM customers c;
END;
$$;

SELECT func2(1);

In Doltgres, this currently returns this error:

ERROR: variable `id` could not be found (errno 1105)

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions