'column reference "id" is ambiguous' error on function call that uses RETURNING

Question

I have this table with two autogenerated columns:

CREATE TABLE driver_orders 
(
    id SERIAL PRIMARY KEY,
    car_id INTEGER NOT NULL,
    location_from GEOGRAPHY  -- generated
        GENERATED ALWAYS AS
            (create_point_geography(start_point)) stored,
    location_to GEOGRAPHY    -- generated
        GENERATED ALWAYS AS
            (create_point_geography(end_point)) stored
);

I am writing a postgres function that inserts a new row then returns id, location_from and location_to. I am following the answers to this question

Here is what I have come up with (unrelated parts removed):

CREATE OR REPLACE FUNCTION create_driver_order(
    car_id INTEGER,
    s_point VARCHAR,
    e_point VARCHAR
  )
  RETURNS TABLE (
    id INTEGER, 
    location_from GEOGRAPHY, 
    location_to GEOGRAPHY
  )
    LANGUAGE plpgsql
AS $$
    DECLARE
    active_order_count INTEGER;
  BEGIN
    -- 1. count active orders
    --     ... omitted unrelated parts
    -- 2. check if active orders are less than two
    --     ... omitted unrelated parts

    -- 4. create order
    RETURN QUERY INSERT INTO driver_orders (
        car_id,
        start_point,
        end_point
      ) VALUES (
        car_id,
        s_point,
        e_point
      ) RETURNING id, location_from, location_to;
  END;
$$

Function is created successfully. But when I call it:

select * from create_driver_order(
    889,
  '(5581326278118 29.220418907676738)'::VARCHAR,
  '(5581326274318 29.220548907676738)'::VARCHAR
)

I get this error:

column reference "id" is ambiguous

Can someone point out what I am doing wrong?

Answer 1

When using returns table the columns of that output table are defined in the scope of your function body, so your use of returning id, location_from, location_to could either refer to columns of the output record, or to columns of the table you're inserting to, or even something else. Just specify which one you want, explicitly. Demo:

CREATE OR REPLACE FUNCTION create_driver_order(car_id INTEGER,
                                               s_point VARCHAR,
                                               e_point VARCHAR)
  RETURNS TABLE (id INTEGER, 
                 location_from GEOGRAPHY, 
                 location_to GEOGRAPHY)
  LANGUAGE plpgsql AS $f$
DECLARE
    active_order_count INTEGER;
BEGIN
    -- 1. count active orders
    --     ... omitted unrelated parts
    -- 2. check if active orders are less than two
    --     ... omitted unrelated parts

    -- 4. create order
    RETURN QUERY INSERT INTO driver_orders AS d (
        car_id,
        start_point,
        end_point
      ) VALUES (
        car_id,
        s_point,
        e_point
      ) RETURNING d.id, 
                  d.location_from, 
                  d.location_to;
END $f$;