CODEWITHSUNDEEP
postgresqldatabase-triggerpostgresql-9.6
hyphen
hyphen

Reputation: 3430

PostgreSQL missing from clause entry for table in function

I have the following function that handles a trigger on insert or update:

CREATE OR REPLACE FUNCTION ticketChangeFunc() RETURNS TRIGGER AS $$
   BEGIN
        INSERT INTO dw.FactSalesHeader (DateKey, LocationKey, EmployeeKey, AppointmentTypeKey, TicketStatusTypeKey, TicketID,TotalAmount, IsNewPatient, IsActive)
        SELECT d.date_key, COALESCE(l.LocationKey, 0), COALESCE(e.EmployeeKey, 0), COALESCE(a.AppointmentTypeKey, 0), COALESCE(ts.TicketStatusTypeKey, 0), NEW.ticket_id,
        NEW.total_amount, NEW.is_new_patient, NEW.is_active
        FROM db1.tickets t
        JOIN dw.DimDate d on t.ticket_date = d.db_date
        LEFT JOIN dw.DimLocation l on NEW.location_id = l.LocationID
        LEFT JOIN dw.DimEmployee e on NEW.counselor_id = e.EmployeeID
        LEFT JOIN dw.DimAppointmentType a on NEW.office_visit_ind = a.AppointmentTypeFlagAttribute
        LEFT JOIN dw.DimTicketStatus ts on NEW.ticket2_status = ts.TicketStatusTypeID
        ON CONFLICT (TicketID)
        DO UPDATE
        SET DateKey = d.date_key, 
        LocationKey = COALESCE(l.LocationKey, 0), 
        EmployeeKey = COALESCE(e.EmployeeKey, 0), 
        AppointmentTypeKey = COALESCE(a.AppointmentTypeKey, 0), 
        TicketStatusTypeKey = COALESCE(ts.TicketStatusTypeKey, 0), 
        TotalAmount = NEW.total_amount, 
        IsNewPatient = NEW.is_new_patient, 
        IsActive = NEW.is_active;
      RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

I get the error: missing FROM-clause entry for table "d"

I thought maybe it was somehow related to the db1.tickets table that I'm not technically using in the query. I've tried taking out that bit and just doing FROM dw.DimDate with WHERE d.date_key = NEW.ticket_date, and that gives the same error.

There are a lot of these types of questions on SO, but I haven't found one that addresses this particular scenario, as I feel like it has something to do with it being inside of this trigger function.

The function gets called from this:

CREATE TRIGGER trg_tickets AFTER INSERT OR UPDATE ON db1.tickets
FOR EACH ROW EXECUTE PROCEDURE ticketChangeFunc();

I'm also wondering if using the ON CONFLICT UPDATE clause updates every row when it finds a match regardless of whether the values differ? Is there a performance impact to this, and if so, is there a way to check for equality of each field and do nothing if there are no differences?

Upvotes: 1

Views: 1122

Answers (1)

hyphen
hyphen

Reputation: 3430

For those who may happen to run into this in the future, the solution for me was to add a conditional to the function and handle inserts separately from updates:

CREATE OR REPLACE FUNCTION ticketChangeFunc() RETURNS TRIGGER AS $$
   BEGIN
        IF TG_OP = 'INSERT' THEN
            INSERT INTO dw.FactSalesHeader (DateKey, LocationKey, EmployeeKey, AppointmentTypeKey, TicketStatusTypeKey, TicketID, TotalAmount, IsNewPatient, IsActive)
            SELECT d.date_key, COALESCE(l.LocationKey, 0), COALESCE(e.EmployeeKey, 0), COALESCE(a.AppointmentTypeKey, 0), COALESCE(ts.TicketStatusTypeKey, 0), NEW.ticket_id, 
            NEW.total_amount, NEW.is_new_patient, NEW.is_active
            FROM db1.tickets t
            JOIN dw.DimDate d ON NEW.ticket_date = d.db_date
            LEFT JOIN dw.DimLocation l ON NEW.location_id = l.LocationID
            LEFT JOIN dw.DimEmployee e ON NEW.counselor_id = e.EmployeeID
            LEFT JOIN dw.DimAppointmentType a ON NEW.office_visit_ind = a.AppointmentTypeFlagAttribute
            LEFT JOIN dw.DimTicketStatus ts ON NEW.ticket2_status = ts.TicketStatusTypeID;        
        ELSE
            UPDATE dw.FactSalesHeader 
            SET DateKey = CAST(TO_CHAR(NEW.ticket_date, 'YYYYMMDD') as integer),
            LocationKey = COALESCE(l.LocationKey, 0), 
            EmployeeKey = COALESCE(e.EmployeeKey, 0),
            AppointmentTypeKey = COALESCE(a.AppointmentTypeKey, 0),
            TicketStatusTypeKey = COALESCE(ts.TicketStatusTypeKey, 0),
            TotalAmount = NEW.total_amount, 
            IsNewPatient = NEW.is_new_patient, 
            IsActive = NEW.is_active
            FROM dw.FactSalesHeader hdr
            LEFT JOIN dw.DimLocation l ON NEW.location_id = l.LocationID
            LEFT JOIN dw.DimEmployee e ON NEW.counselor_id = e.EmployeeID
            LEFT JOIN dw.DimAppointmentType a ON NEW.office_visit_ind = a.AppointmentTypeFlagAttribute
            LEFT JOIN dw.DimTicketStatus ts ON NEW.ticket2_status = ts.TicketStatusTypeID
            WHERE hdr.TicketID = NEW.ticket_id;
        END IF;        
    RETURN NULL;
   END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions