创建返回JSON的Postgres函数

Question

我已经创建了一个查询来返回数据作为对象数组，它工作得很好，但我不能让它作为Postgres函数工作，所以我可以在其他查询中调用它来返回包含数组的列。

SELECT json_agg(json_build_object(
'first_name', u.first_name,
'last_name', u.last_name,
'username', u.username,
'photo', p.filename
)) friends
FROM events e
INNER JOIN user_events ue ON ue.event_uid::uuid = e.uid
INNER JOIN users u ON u.uid = ue.user_uid::uuid
INNER JOIN user_followers uf ON uf.user_uid::uuid = u.uid
LEFT JOIN photographs p on p.uid = u.profile_photograph_uid::uuid
WHERE e.uid = '00000000-0000-0000-0000-000000000000'
AND uf.follower_uid = '00000000-0000-0000-0000-000000000000';

我创建的函数是this，当我将其添加到查询中时，它会抛出错误。

CREATE OR REPLACE FUNCTION friends_attending(event_uid uuid, user_uid uuid)
RETURNS TABLE
(
friends character
)
language plpgsql
AS
$$
BEGIN
RETURN QUERY
SELECT json_agg(json_build_object(
'first_name', u.first_name,
'last_name', u.last_name,
'username', u.username,
'photo', p.filename
)) friends
FROM events e
INNER JOIN user_events ue ON ue.event_uid::uuid = e.uid
INNER JOIN users u ON u.uid = ue.user_uid::uuid
INNER JOIN user_followers uf ON uf.user_uid::uuid = u.uid
LEFT JOIN photographs p on p.uid = u.profile_photograph_uid::uuid
WHERE e.uid = $1
AND uf.follower_uid = $2;
END;
$$;

我在另一个查询中使用这个函数。

friends_attending(e.uid, '00000000-0000-0000-0000-000000000000')

返回错误

[42883] ERROR: operator does not exist: character = uuid 
Hint: No operator matches the given name and argument types. You might need to add explicit type casts. 
Where: PL/pgSQL function friends_attending(uuid,uuid) line 3 at RETURN QUERY

我试过在函数的各个部分添加类型强制转换，但似乎都不起作用。

Answer 1

返回的数据被设置为'character'而不是'json' &WHERE中的最后一个子句需要被强制转换为uid。

CREATE OR REPLACE FUNCTION friends_attending(event_uid uuid, user_uid uuid)
RETURNS TABLE
(
friends json
)
language plpgsql
AS
$$
BEGIN
RETURN QUERY
SELECT json_agg(json_build_object(
'first_name', u.first_name,
'last_name', u.last_name,
'username', u.username,
'photo', p.filename
)) friends
FROM events e
INNER JOIN user_events ue ON ue.event_uid::uuid = e.uid
INNER JOIN users u ON u.uid = ue.user_uid::uuid
INNER JOIN user_followers uf ON uf.user_uid::uuid = u.uid
LEFT JOIN photographs p on p.uid = u.profile_photograph_uid::uuid
WHERE e.uid = $1
AND uf.follower_uid::uuid = $2;
END;
$$;