ERROR: invalid input syntax for type uuid on wrong column

I am in progress of writing a function that takes 2 objects. My goal is to have the restaurant be optionally inserted if it does not exist followed by inserting the food item into its respective tables. I need to write a function because I am using postgraphile to make the request to insert the data.

Here is my SQL schema definition:

CREATE SCHEMA IF NOT EXISTS public;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

-- Restaurant table
CREATE TABLE IF NOT EXISTS restaurants
(   id uuid NOT NULL PRIMARY KEY,
    name citext NOT NULL,
    address citext NOT NULL,
    UNIQUE (name, address)
);

-- Restaurant items table
CREATE TABLE IF NOT EXISTS restaurant_items
(   id uuid NOT NULL PRIMARY KEY,
    name citext NOT NULL,
    restaurant_id uuid NOT NULL,
    UNIQUE (name, restaurant_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants (id)
);

-- Custom types that don't include certain fields
CREATE OR REPLACE VIEW new_restaurant_item AS
    SELECT id, name 
    FROM restaurant_items 
    WHERE false;

CREATE OR REPLACE VIEW restaurant_with_new_item AS
    SELECT r.name AS rest_name, r.address, ri.* 
    FROM restaurants r, restaurant_items ri 
    WHERE false;

CREATE OR REPLACE FUNCTION add_restaurant_item_with_restaurant(
    p_restaurant restaurants,
    p_restaurant_item new_restaurant_item
) RETURNS restaurant_with_new_item AS 
$$
    DECLARE inserted_restaurant restaurants;
    DECLARE inserted_restaurant_item restaurant_items;
BEGIN
    INSERT INTO restaurants(id, name, address)
    VALUES (p_restaurant.id, p_restaurant.name, p_restaurant.address)
    ON CONFLICT ON CONSTRAINT restaurants_name_address_key
        DO UPDATE SET name = EXCLUDED.name
    RETURNING name AS rest_name, address INTO inserted_restaurant;
    
    INSERT INTO restaurant_items(id, name, restaurant_id)
    VALUES (p_restaurant_item.id, p_restaurant_item.name, p_restaurant.id)
    RETURNING *, inserted_restaurant INTO inserted_restaurant_item;

    RETURN inserted_restaurant_item;
END;
$$ LANGUAGE 'plpgsql';

I would rather not have to keep redundantly specifying columns as much as possible, which is why I opted for a custom view that acts as a type for the parameters for the function. When I go to execute the function through both postgraphile and through normal SQL, it states the following with the given query:

SELECT * FROM add_restaurant_item_with_restaurant(
    ROW('a81bc81b-dead-4e5d-abff-90865d1e13b1', 'abc', 'def'),
    ROW('a81bc81b-dead-4e5d-abff-90865d1e13b2', 'abcd')
);

--Result:
ERROR:  invalid input syntax for type uuid: "abc"
CONTEXT:  PL/pgSQL function add_restaurant_item_with_restaurant(restaurants,new_restaurant_item) line 5 at SQL statement
SQL state: 22P02

Oddly if the first field (the actual uuid) is not a valid uuid it complains about that field. And I only have one uuid field on the table. I am kinda stumped. Any idea on why this is happening?

CodePudding user response：

The immediate problem is that the ROW() constructor creates an anonymous type. You can solve this by casting it to the correct type, as in:

ROW('a81bc81b-dead-4e5d-abff-90865d1e13b1', 'abc', 'def')::restaurants

This works because PostgreSQL creates a type with the same name as each table that is created.

The second parameter, new_restaurant_item, is more problematic because this is the name of a view with no contents (WHERE false). Instead of creating a phoney view like that, create types for the two views and you should be good.

CodePudding user response：

Looks like I solved it! Patrick's answer helped me slightly however there was a deeper problem with the query itself it seems. After trying a bunch of different things, I ended up using the types like Patrick suggested and replacing the inserted_restaurant here

INSERT INTO restaurant_items(id, name, restaurant_id)
    VALUES (p_restaurant_item.id, p_restaurant_item.name, p_restaurant.id)
    RETURNING *, inserted_restaurant INTO inserted_restaurant_item;

with inserted_restaurant.* while also manually specifying the other fields to match the new type I added. This change seems fix it completely as with all the other changes I removed it temporarily to see what would happen and it is giving me a similar problem, but with the entire set/row.

I also noticed that the type of the variable inserted_restaurant_item was wrong and didn't encapsulate the type of restaurant and restaurant item with how I am returning it which might have been a factor.

Together the types and function definition look like this:

CREATE TYPE new_restaurant_item AS (id uuid, name citext);
CREATE TYPE new_item_with_restaurant AS (
    id uuid,
    name citext,
    rest_id uuid,
    rest_name citext,
    address citext
);

-- Add restaurant if it doesn't exist and the food associated with it
CREATE OR REPLACE FUNCTION add_restaurant_item_with_restaurant(
  p_restaurant restaurants,
  p_restaurant_item new_restaurant_item
)
RETURNS new_item_with_restaurant AS $$
  DECLARE inserted_restaurant restaurants;
  DECLARE inserted_restaurant_item new_item_with_restaurant;
BEGIN
  INSERT INTO restaurants(id, name, address)
    VALUES (p_restaurant.id, p_restaurant.name, p_restaurant.address)
    ON CONFLICT ON CONSTRAINT restaurants_name_address_key DO UPDATE SET name = EXCLUDED.name
      RETURNING id AS rest_id, name AS rest_name, address INTO inserted_restaurant;
    
  INSERT INTO restaurant_items(id, name, restaurant_id)
    VALUES (p_restaurant_item.id, p_restaurant_item.name, p_restaurant.id)
    RETURNING id, name, inserted_restaurant.* INTO inserted_restaurant_item;

  RETURN inserted_restaurant_item;
END;
$$ LANGUAGE 'plpgsql';

Thank you, Patrick, for leading me down the right direction!