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@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