Column Name Ambiguous Despite Referencing to Table

Admin 1
Viewed 184

Despite not having this problem in a very similar function, I seem to have it in this one and cannot find any advice on the issue in this specific context (that I can comprehend lol). If anyone can give any advice or references that would be greatly appreciated.

ERROR: "column reference "customerid" is ambiguous"

CREATE OR REPLACE FUNCTION update_FlightBooking(INTEGER, INTEGER, INTEGER, INTEGER, CHAR(1), TIMESTAMP, DECIMAL)
RETURNS TABLE
(
  BookingID INTEGER, 
  CustomerID INTEGER,
  FlightID INTEGER, 
  NumSeats INTEGER,
  Status CHAR(1),
  BookingTime TIMESTAMP,
  TotalCost DECIMAL
) AS $$

BEGIN
  IF NOT EXISTS (SELECT CustomerID FROM FlightBooking WHERE FlightBooking.CustomerID = $2) THEN
    INSERT INTO FlightBooking VAlUES($1, $2, $3, $4, $5, $6, $7);

  ELSE
    UPDATE FlightBooking AS fb
    SET NumSeats = NumSeats + $4, TotalCost = TotalCost + $7
    WHERE fb.CustomerID = $2;
  END IF;

  IF NOT EXISTS (SELECT CustomerID FROM LeadCustomer WHERE LeadCustomer.CustomerID = $2) THEN
    ROLLBACK;
  END IF;

  IF (check_Seats($3) < 0) THEN 
    ROLLBACK; 
  END IF;

  RETURN QUERY
    SELECT fb.BookingID, fb.CustomerID, fb.FlightID, fb.NumSeats, fb.Status, fb.BookingTime, fb.TotalCost 
    FROM FlightBooking AS fb WHERE fb.CustomerID = $1;
END;
$$
LANGUAGE plpgsql;
postgresql sql
1 Answers

https://www.postgresql.org/docs/9.5/plpgsql-implementation.html says

By default, PL/pgSQL will report an error if a name in a SQL statement could refer to either a variable or a table column. You can fix such a problem by renaming the variable or column, or by qualifying the ambiguous reference, or by telling PL/pgSQL which interpretation to prefer.

This applies to customerId in IF NOT EXISTS (SELECT CustomerID ...

user_107268500
Related