Re: The output sql generated by pg_dump for a create function refers to a modified table name

On 2/17/23 10:09 AM, Tom Lane wrote:
> "Jonathan S. Katz" <jkatz(at)postgresql(dot)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