On 2/17/23 10:09 AM, Tom Lane wrote:
"Jonathan S. Katz" <jk...@postgresql.org> writes:Good catch. Do you have thoughts on how we can adjust the naming logic to handle cases like this?I think it's perfectly fine that ruleutils decided to use different aliases for the two different occurrences of "t1": the statement is quite confusing as written.
Agreed on that -- while it's harder to set up, I do prefer the original example[1] to demonstrate this, as it shows the issue given it does not have those multiple occurrences, at least not within the same context, i.e.:
CREATE OR REPLACE FUNCTION public.calendar_manage(room_id int, calendar_date date)
RETURNS void
LANGUAGE SQL
BEGIN ATOMIC
WITH delete_calendar AS (
DELETE FROM calendar
WHERE
room_id = $1 AND
calendar_date = $2
)
INSERT INTO calendar (room_id, status, calendar_date, calendar_range)
SELECT $1, c.status, $2, c.calendar_range
FROM calendar_generate_calendar($1, tstzrange($2, $2 + 1)) c;
END;
the table prefixes on the attributes within the DELETE statement were
ultimately mangled:
WITH delete_calendar AS (
DELETE FROM public.calendar
WHERE ((calendar_1.room_id OPERATOR(pg_catalog.=)
calendar_manage.room_id) AND (calendar_1.calendar_date
OPERATOR(pg_catalog.=) calendar_manage.calendar_date))
)
INSERT INTO public.calendar (room_id, status, calendar_date,
calendar_range)
The problem probably is that get_delete_query_def() has no idea that it's supposed to print the adjusted alias just after "DELETE FROM tab". UPDATE likely has same issue ... maybe INSERT too?
Maybe? I modified the function above to do an INSERT/UPDATE instead of a DELETE but I did not get any errors. However, if the logic is similar there could be an issue there.
Thanks, Jonathan[1] https://www.postgresql.org/message-id/e947fa21-24b2-f922-375a-d4f763ef3e4b%40postgresql.org
OpenPGP_signature
Description: OpenPGP digital signature
