我在SQL Server中有一个过程，如下所示：
我试图写一个像上面一样的postgresql函数。我尝试过这样的事情：
CREATE OR REPLACE FUNCTION getadmbyyear(IN a integer, IN b character varying)
RETURNS TABLE(monname character varying, mon integer, adm integer, selected integer) AS
$BODY$
DECLARE testtypeid int;
select top 1 testtypeid := typeid from Reports_ReportMenu
where ID = $2
select MonName,Mon,Adm,
case when ROW_NUMBER() OVER(ORDER BY Mon,Adm) = '1' then
cast(1 as int) else cast(0 as int) end as Selected
from eivTestAdms
where test_type_id=testtypeid and "YR"=$1 and Adm is not null
order by Mon,Adm
$BODY$
LANGUAGE sql;
在这里，我在int的声明语句中遇到语法错误。我是否以正确的格式编写函数？
您不能在SQL函数中使用变量，这些变量仅在使用PL / pgSQL时可用。
但是无论如何都不需要该变量。您可以将其放入子选择中。
另外cast(1 as int)是没用的。1已经是整数，所以0那里不需要强制转换。
CREATE OR REPLACE FUNCTION getadmbyyear(IN a integer, IN b character varying)
RETURNS TABLE(monname character varying, mon integer, adm integer, selected integer)
AS
$body$
select MonName,
Mon,
Adm,
case
when ROW_NUMBER() OVER (ORDER BY Mon,Adm) = 1 then 1
else 0
end as Selected
from eivTestAdms
where test_type_id = (select testtypeid
from Reports_ReportMenu
where ID = $2
limit 1)
and "YR"=$1
and Adm is not null
order by Mon,Adm;
$body$
language sql;
但是SQL Server的bit数据类型通常用作穷人的布尔值。因此，boolean在Postgres中可能应该是真实的。这使得case语句更短：
select MonName,
Mon,
Adm,
ROW_NUMBER() OVER (ORDER BY Mon,Adm) = 1 as selected -- this is a boolean expression that will return true or false
from eivTestAdms
然后，您需要将功能签名调整为：
RETURNS TABLE(monname character varying, mon integer, adm integer, selected boolean)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句