-
-
Notifications
You must be signed in to change notification settings - Fork 51
Open
Labels
Description
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)Reactions are currently unavailable