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

[Jonathan S. Katz]

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

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;

CREATE OR REPLACE FUNCTION public.calendar_manage(room_id int, calendar_date date)

 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.

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 (

)

    c.status,
    calendar_manage.calendar_date,
    c.calendar_range


Thanks,

Jonathan

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) (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, 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);

OpenPGP_signature
Description: OpenPGP digital signature