sql - 使用 sql 返回多列：错误查询没有结果数据的目的地

Question

嘿，

一直在寻找解决此问题的方法，但没有运气。我想返回多列。

错误代码：

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function name(character varying) line 3 at SQL statement
SQL state: 42601

我的代码：

CREATE OR REPLACE FUNCTION name(vn CHARACTER VARYING)
    RETURNS CHARACTER VARYING AS $$
BEGIN
SELECT
    (SELECT row FROM TableName AS tn WHERE tn.viitenumber = vn) AS vn1,
    (SELECT row FROM TableName AS tn WHERE tn.viitenumber = vn) AS vn2;
END; $$
LANGUAGE plpgsql

我能够返回一列

RETURN(SELECT TableName FROM lepingu_lisad AS ll WHERE ll.viitenumber = vn) AS vn1;

但我如何返回两个列

标签： sqlpostgresqlplpgsql

Answer 1

CREATE OR REPLACE FUNCTION name
(vn CHARACTER VARYING,
 OUT vn1 CHARACTER VARYING,
 OUT vn2 CHARACTER VARYING) AS $$
BEGIN
SELECT
    SELECT row into vn1 FROM TableName AS tn WHERE tn.viitenumber = vn;
    SELECT row into vn2 FROM TableName AS tn WHERE tn.viitenumber = vn;
END; $$
LANGUAGE plpgsql;

用法是：

select (name_results).vn1, (name_results).vn2 from name(_vn) name_results;