函数在postgres中返回多行,并返回一些空值
问题描述:
我在函数postgress方法中没有什么问题。 我有功能:函数在postgres中返回多行,并返回一些空值
CREATE OR REPLACE FUNCTION test(
x integer,
y character varying,
z character varying
)
RETURNS TABLE(x1 int, y1 varchar,n1 varchar,z1 varchar) AS
$BODY$
BEGIN
RETURN QUERY
SELECT x,y,null,z FROM example_tab
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
当我执行这个功能我把错误:
Error: Returned type unknown does not match expected type character varying in column 3.
SELECT * FROM test(694531020,'t'::varchar,'t'::varchar)
我应该怎么做才能正确运行此查询,并执行该功能???
答
应明确指定数据类型为空,如:
CREATE OR REPLACE FUNCTION test(
x integer,
y character varying,
z character varying
)
RETURNS TABLE(x1 int, y1 varchar,n1 varchar,z1 varchar) AS
$BODY$
BEGIN
RETURN QUERY
SELECT x,y,null::varchar,z FROM example_tab
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
+0
感谢它的工作原理! – Piateczka18
'选择X,Y,空:: VARCHAR,z的example_tab'并请从标签删除MySQL和SQL服务器 –