使用 plpgsql 的 'RETURN SETOF' 返回带有 join 的简单查询

Question

我在postgresql中的返回值时遇到了麻烦，我创建了这样的函数（编辑）：

CREATE OR REPLACE FUNCTION GetComissionamento(pv varchar(50))
RETURNS SETOF tb_comissionamentos AS
$$
BEGIN
SELECT com.* FROM tb_vendas as vendas 
 INNER JOIN tb_pagamentos as pg on vendas.id_venda = pg.id_venda
 INNER JOIN tb_comissionamentos as com on vendas.id_venda = com.id_venda 
    AND com.id_pagamento = pg.id_pagamento
 WHERE id_venda_imobiliaria = pv;
END
$$
LANGUAGE plpgsql;

，我想如查询所说的那样返回表Tb_comessionmento上的数据。我应该怎么办？当我调用功能时，我收到了错误消息：

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

Answer 1

这是不好的。

1. 这应该是VIEW而不是函数

这样，

CREATE VIEW GetComissionamento AS 
SELECT com.* FROM tb_vendas as vendas 
     INNER JOIN tb_pagamentos as pg on vendas.id_venda = pg.id_venda
     INNER JOIN tb_comissionamentos as com on vendas.id_venda = com.id_venda 
        AND com.id_pagamento = pg.id_pagamento;

，然后（其中$ PV是您的价值），

SELECT *
FROM GetComissionamento
WHERE id_venda_imobiliaria = $pv; 

2. 如果您要使此简单的功能作为功能，请使用sql而不是plpgsql

这样，

CREATE FUNCTION GetComissionamento(varchar(50))
RETURNS tb_comissionamentos AS $$
SELECT com.* FROM tb_vendas as vendas 
 INNER JOIN tb_pagamentos as pg on vendas.id_venda = pg.id_venda
 INNER JOIN tb_comissionamentos as com on vendas.id_venda = com.id_venda 
    AND com.id_pagamento = pg.id_pagamento
 WHERE id_venda_imobiliaria = $1;
$$ LANGUAGE SQL;

3. 如果您将其保留为PLPGSQL，请使用RETURN QUERY。

这样，

CREATE OR REPLACE FUNCTION GetComissionamento (pv varchar(50))
RETURNS tb_comissionamentos AS $$
BEGIN
  RETURN QUERY
     SELECT com.* FROM tb_vendas as vendas 
     INNER JOIN tb_pagamentos as pg on vendas.id_venda = pg.id_venda
     INNER JOIN tb_comissionamentos as com on vendas.id_venda = com.id_venda 
        AND com.id_pagamento = pg.id_pagamento
     WHERE id_venda_imobiliaria = pv;
  -- error checking or whatever
  RETURN;
END;
$$ LANGUAGE plpgsql;

Answer 2

是的，出现了错误，忘记了返回查询

CREATE OR REPLACE FUNCTION GetComissionamento(pv varchar(50))
RETURNS SETOF tb_comissionamentos AS
$$
BEGIN
RETURN QUERY SELECT com.* FROM tb_vendas as vendas 
 INNER JOIN tb_pagamentos as pg on vendas.id_venda = pg.id_venda
 INNER JOIN tb_comissionamentos as com on vendas.id_venda = com.id_venda 
    AND com.id_pagamento = pg.id_pagamento
 WHERE id_venda_imobiliaria = pv;
END
$$
LANGUAGE plpgsql;