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

On 2/17/23 11:19 AM, Jonathan S. Katz wrote:
> On 2/17/23 10:09 AM, Tom Lane wrote:

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

I spoke too soon -- I was looking at the wrong logs. I did reproduce it 
with UPDATE, but not INSERT. The example I used for UPDATE:

CREATE OR REPLACE FUNCTION public.calendar_manage(room_id int, 
calendar_date date)
RETURNS void
LANGUAGE SQL
BEGIN ATOMIC
     WITH update_calendar AS (
         UPDATE calendar
         SET room_id = $1
         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;

which produced:

WITH update_calendar AS (
     UPDATE public.calendar SET room_id = calendar_manage.room_id
         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)  SELECT calendar_manage.room_id,
     c.status,
     calendar_manage.calendar_date,
     c.calendar_range
FROM public.calendar_generate_calendar(calendar_manage.room_id, 
pg_catalog.tstzrange((calendar_manage.calendar_date)::timestamp with 
time zone, ((calendar_manage.calendar_date OPERATOR(pg_catalog.+) 
1))::timestamp with time zone)) c(status, calendar_range);

Thanks,

Jonathan