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