Why does Postgresql function not work as expected-CodePudding

I have table like this

 id|    booking_id       |       status         |         message             |          created_at
 ----------------------------------------------------------------------------------------------------------
 1 | 1467                | VALIDATE             | Validate Payment            | 2022-03-18 07:43:20.688 00
 2 | 1901                | SUCCESS              |                             | 2022-03-18 07:51:58.072 00
 3 | 1847                | FAILED               | payment is insufficient     | 2022-03-18 08:16:05.79 00

I create get function like this

CREATE OR REPLACE FUNCTION get_booking_status(booking_id BIGINT)
  RETURNS JSONB
  LANGUAGE SQL
  SECURITY DEFINER
AS $$
SELECT to_jsonb(result)
FROM (SELECT * FROM booking.booking_status WHERE booking.booking_status.booking_id = booking_id) AS result
$$;

I tried to call function like this

SELECT get_booking_status(1467)

but return json all 3 row instead of 1 row with booking_id 1467

can you explain root cause ?

CodePudding user response：

The root cause of you problem is naming your parameter (booking_id) the same as a column name (booking_id) in your table. When processing a query any unqualified reference follows a hierarchy to determine the correct reference; the first being the table column. In this case where booking.booking_status.booking_id = booking_id both references to booking_id are interrupted as referring to the table column. WHich it then true for eaery row in the table. You have basically 2 choices: change the parameter name or qualify with the function name.

CREATE OR REPLACE FUNCTION get_booking_status(booking_id BIGINT)
  RETURNS JSONB
  LANGUAGE SQL
  SECURITY DEFINER
AS $$
SELECT to_jsonb(result)
  FROM (SELECT * 
          FROM booking.booking_status 
         WHERE booking.booking_status.booking_id 
             = get_booking_status.booking_id) AS result
$$;

Also see comments from @a_horse_with_no_name and @AdrianKlaver above.