PostgreSQL创建存储过程后执行报错：Prepared Statement不存在

我用以下SQL创建了名为get_conferences_for_attendee的存储过程：

CREATE PROCEDURE get_conferences_for_attendee
(
  IN start_time TIMESTAMP,
  IN end_time TIMESTAMP,
  IN email VARCHAR(255),
  IN deleted BOOLEAN
)
AS
$$
  SELECT c.localuuid, c.title, i.id, i.start_time, i.end_time, i.status, a.email, a.deleted
  FROM Conference c
  INNER JOIN Instance i ON i.conference_localuuid = c.localuuid
  INNER JOIN Conference_Attendees ca ON ca.conference_localuuid = c.localuuid
  INNER JOIN Attendee a ON ca.attendees_localuuid = a.localuuid
  WHERE i.start_time BETWEEN start_time AND end_time
  AND a.email = email
  AND a.deleted = deleted
$$ LANGUAGE SQL;

执行后返回CREATE PROCEDURE，且通过查询pg_proc能看到该存储过程：

SELECT proname, prorettype
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

查询结果：

proname            | prorettype 
------------------------------+------------
 get_conferences_for_attendee |       2278

但使用EXECUTE语句调用时出现错误：

EXECUTE get_conferences_for_attendee ('2022-12-26T00:00:00', '2023-01-01T23:59:59', 'yacs.demo2@abc.com', false);

错误信息：

ERROR: prepared statement "get_conferences_for_attendee" does not exist

自行尝试的解决方案 #

我找到了一种可行方案，但不确定是否规范，感觉过于复杂：

CREATE TYPE conference_record AS (                                                                                     
  localuuid VARCHAR(255),
  title VARCHAR(255),
  id VARCHAR(255),
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  status VARCHAR(255),
  email VARCHAR(255),
  deleted BOOLEAN
);

CREATE FUNCTION get_conferences_for_attendee
(
  IN start_time TIMESTAMP,
  IN end_time TIMESTAMP,
  IN email VARCHAR(255),
  IN deleted BOOLEAN
)
RETURNS SETOF conference_record AS $$
  BEGIN
    RETURN QUERY
    SELECT c.localuuid, c.title, i.id, i.start_time, i.end_time, i.status, a.email, a.deleted
    FROM Conference c
    INNER JOIN Instance i ON i.conference_localuuid = c.localuuid
    INNER JOIN Conference_Attendees ca ON ca.conference_localuuid = c.localuuid
    INNER JOIN Attendee a ON ca.attendees_localuuid = a.localuuid
    WHERE i.start_time BETWEEN $1 AND $2
    AND a.email = $3
    AND a.deleted = $4;
  END;
$$ LANGUAGE plpgsql;

使用以下语句可以正常查询：

SELECT * FROM get_conferences_for_attendee ('2022-12-26T00:00:00', '2023-01-01T23:59:59', 'yacs.demo1@abc.com', false);

疑问 #

请问这种解决方案是否规范？或者有没有更合适的方式解决最初的存储过程调用报错问题？

内容的提问来源于stack exchange，提问作者Umut Emre Önder