PostgreSQL函数返回表或SETOF

Quick background: very new to PostgreSQL, came from SQL Server. I am working on converting stored procedures from SQL Server to PostgreSQL. I have read Postgres 9.3 documentation and looked through numerous examples and questions but still cannot find a solution.

快速背景:对PostgreSQL来说很新,来自SQL Server。我正在努力将存储过程从SQL Server转换为PostgreSQL。我阅读了Postgres 9.3文档并查看了大量示例和问题,但仍无法找到解决方案。

I have this select statement that I execute weekly to return new values

我有这个select语句,我每周执行一次以返回新值

select distinct id, null as charges
     , generaldescription as chargedescription, amount as paidamount
from mytable
where id not in (select id from myothertable)

What can I do to turn this into a function where I can just right click and execute on a weekly basis. Eventually I will automate this using a program another developer built. So it will execute with no user involvement and send results in a spreadsheet to the user. Not sure if that last part matters to how the function is written.

我该怎么做才能把它变成一个我可以每周点击并执行的功能。最终,我将使用另一个开发人员构建的程序自动执行此操作。因此,它将在没有用户参与的情况下执行,并将结果通过电子表格发送给用户。不确定最后一部分是否与函数的编写有关。

This is one of my many failed attempts:

这是我失败的尝试之一:

CREATE FUNCTION newids
RETURNS TABLE (id VARCHAR, charges NUMERIC
             , chargedescription VARCHAR, paidamount NUMERIC) AS Results
Begin
SELECT DISTINCT id, NULL AS charges
     , generaldescription AS chargedescription, amount AS paidamount
FROM mytable
WHERE id NOT IN (SELECT id FROM myothertable)
END;
$$ LANGUAGE plpgsql;

Also I am using Navicat and the error is:

我也在使用Navicat,错误是:

function result type must be specified

必须指定函数结果类型

1 个解决方案

#1

You can use PL/pgSQL here and it may even be the better choice.

你可以在这里使用PL / pgSQL,它甚至可能是更好的选择。

Difference between language sql and language plpgsql in PostgreSQL functions

PostgreSQL函数中语言sql和语言plpgsql的区别

But you need to fix a syntax error, match your dollar-quoting, add an explicit cast (NULL::numeric) and use RETURN QUERY:

但是你需要修复语法错误,匹配你的美元引用,添加一个显式的强制转换(NULL :: numeric)并使用RETURN QUERY:

CREATE FUNCTION newids_plpgsql()
  RETURNS TABLE (
     id         varchar
   , charges    numeric
   , chargedescription varchar
   , paidamount numeric
   ) AS  -- Results  -- remove this
$func$
BEGIN

RETURN QUERY
SELECT ...;

END;
$func$ LANGUAGE plpgsql;

Or use a simple SQL function:

或者使用简单的SQL函数:

CREATE FUNCTION newids_sql()
  RETURNS TABLE (
     id         varchar
   , charges    numeric
   , chargedescription varchar
   , paidamount numeric
   ) AS
$func$
SELECT ...;
$func$ LANGUAGE sql;

Either way, the SELECT statement can be more efficient:

无论哪种方式,SELECT语句都可以更高效:

SELECT DISTINCT t.id, NULL::numeric AS charges
     , t.generaldescription AS chargedescription, t.amount AS paidamount
FROM   mytable t
LEFT   JOIN myothertable m ON m.id = t.id
WHERE  m.id IS NULL;

Select rows which are not present in other table

选择其他表中不存在的行

Of course, all data types must match. I can't tell, table definition is missing.
And are you sure you need DISTINCT?

当然,所有数据类型必须匹配。我不知道,表定义缺失。你确定你需要DISTINCT吗?

#1