从PostgreSQL函数返回一个复合类型或多个列

Question

我的目标是编写一个接受一个参数并返回两个值的函数。查询运行得很好，但是，当通过make函数执行时，我收到一个错误，即子查询不应返回多列。

我的功能如下：

CREATE TYPE double_integer_type AS (p1 integer, p2 integer);
DROP FUNCTION next_dvd_in_queue;
CREATE OR REPLACE FUNCTION next_dvd_in_queue (member_id_p1 integer) RETURNS double_integer_type as $$
BEGIN
RETURN(
select temp2.dvdid,
temp2.movie_title
from
(select temp1.dvdid,
temp1.movie_title,
temp1.customer_priority
from
(select *
from rentalqueue
where rentalqueue.memberid=member_id_p1) temp1
inner join dvd on dvd.dvdid=temp1.dvdid
where dvd.dvdquantityonhand>0) temp2
order by temp2.customer_priority asc
limit 1
);
END; $$ LANGUAGE PLPGSQL

呼叫：

select dvdid from next_dvd_in_queue(3);

当使用硬编码值执行时，查询为：

select temp2.dvdid,
temp2.movie_title
from
(select temp1.dvdid,
temp1.movie_title,
temp1.customer_priority
from
(select *
from rentalqueue
where rentalqueue.memberid=3) temp1
inner join dvd on dvd.dvdid=temp1.dvdid
where dvd.dvdquantityonhand>0) temp2
order by temp2.customer_priority asc
limit 1

上面的查询运行良好。

然而，当我以以下方式调用函数时：

select * from next_dvd_in_queue(3);

我得到以下错误：

ERROR:  subquery must return only one column
LINE 1: SELECT (
^
QUERY:  SELECT (
select temp2.dvdid,
temp2.movie_title
from
(select temp1.dvdid,
temp1.movie_title,
temp1.customer_priority
from
(select *
from rentalqueue
where rentalqueue.memberid=3) temp1
inner join dvd on dvd.dvdid=temp1.dvdid
where dvd.dvdquantityonhand>0) temp2
order by temp2.customer_priority asc
limit 1
)
CONTEXT:  PL/pgSQL function next_dvd_in_queue(integer) line 3 at RETURN

Answer 1

您可以通过显式转换为复合类型来修复语法错误：

CREATE OR REPLACE FUNCTION next_dvd_in_queue (member_id_p1 integer)
RETURNS double_integer_type AS
$func$
BEGIN
RETURN (
SELECTROW(temp2.dvdid, temp2.movie_title)::double_integer_type
FROM  ...
);
END
$func$  LANGUAGE plpgsql

但我会消除复合类型不必要的复杂性，并使用OUT参数：

CREATE OR REPLACE FUNCTION pg_temp.next_dvd_in_queue (member_id_p1 integer
OUT p1 integer
OUT p2 varchar(100)) AS
$func$
BEGIN
SELECT INTO p1, p2
temp2.dvdid, temp2.movie_title
FROM  ...
END
$func$  LANGUAGE plpgsql;

避免参数名称和列名之间的命名冲突。我喜欢坚持一种命名约定，在这里我用_作为所有参数名称的前缀，所以_member_id_p1、_p1、_p2。

相关：

• 从带有OUT参数的函数返回
• 如何在PostgreSQL中返回函数内部的SELECT结果