错误:错误列上uuid类型的输入语法无效- Postgresql 14.2
错误:错误列上uuid类型的输入语法无效- Postgresql 14.2
提问于 2022-05-22 13:59:26
我正在编写一个包含两个对象的函数。我的目标是，如果餐厅不存在，可以随意插入，然后将食物项插入到相应的表中。我需要编写一个函数，因为我使用postgraphile来请求插入数据。
下面是我的SQL模式定义：
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';我不希望尽可能多地冗馀地指定列，这就是为什么我选择了一个自定义视图作为函数参数的类型。当我通过postgraphile和普通SQL执行函数时，它使用给定的查询声明如下：
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奇怪的是，如果第一个字段(实际的uuid)不是有效的uuid，它会抱怨该字段。我只有一个uuid字段在桌子上。我有点困惑。知道为什么会这样吗？
Stack Overflow用户
发布于 2022-05-22 18:17:57
看来我解决了！Patrick的回答略微帮助了我，但是，这个查询本身似乎有一个更深层次的问题。在尝试了一系列不同的东西之后，我最终使用了Patrick建议的类型，并在这里替换了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;使用inserted_restaurant.*，同时还手动指定与我添加的新类型匹配的其他字段。这个更改似乎完全修复了它，就像我暂时删除它以查看会发生什么一样，它给了我一个类似的问题，但是对于整个集合/行。
我还注意到变量inserted_restaurant_item的类型是错误的，并且没有用返回它的方式封装餐馆和餐馆项目的类型，这可能是一个因素。
类型和函数定义的组合如下所示：
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';谢谢你，帕特里克，你把我引向正确的方向！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72338266
复制相关文章
相似问题
腾讯云开发者
