代码之家 › 专栏 › 技术社区 › Vikram

无法在交叉表函数postgres中传递参数

postgresql-9.4 postgresql-9.3 plpgsql postgresql sql

0

Vikram · 技术社区 · 8 年前

我知道我必须在postgres中将文本传递给交叉表函数。但不知为什么我做不到。我不确定我做错了什么,请帮忙。这就是我试图创建的函数

    create or replace function hrms.test2(startdate date)
returns table(
employeeid int,
col1 int,
col2 INT,
col3 int,
col4 int) as
$body$
SELECT * FROM hrms.crosstab(
  $firstquery$ 
  SELECT tms.employeeid,tms.today,count(tms.employeeid) as countid 
  FROM hrms.timesheet as tms
  where dated>=|| quote_literal(startdate) ||
  and dated < ||+ quote_literal(startdate)||::timestamp + '1 MONTH'::INTERVAL
  group by tms.employeeid,tms.today $firstquery$,
  $secquery$ select distinct tms.today 
  from hrms.timesheet as tms$secquery$
)as
finalresult(employeeid int,leave int,present int,absent int, holiday int)
$body$ 
LANGUAGE SQL;

select * from hrms.test2('2017-09-01')

我收到一条错误消息说

column startdate doesn't exist

我还尝试了其他几种选择。我不确定我做错了什么,请帮忙。

2 回复 | 直到 8 年前

1

0

Vao Tsun 8 年前

$firstquery$ ,对于您的代码,我假设您想要smth,例如:

create or replace function hrms.test2(startdate date)
returns table(
employeeid int,
col1 int,
col2 INT,
col3 int,
col4 int) as
$body$
SELECT * FROM hrms.crosstab(
  $firstquery$ 
  SELECT tms.employeeid,tms.today,count(tms.employeeid) as countid 
  FROM hrms.timesheet as tms
  where dated>=$firstquery$|| quote_literal(startdate) ||$firstquery$
  and dated < $firstquery$||+ quote_literal(startdate)||$firstquery$::timestamp + '1 MONTH'::INTERVAL
  group by tms.employeeid,tms.today $firstquery$,
  $secquery$ select distinct tms.today 
  from hrms.timesheet as tms$secquery$
)as
finalresult(employeeid int,leave int,present int,absent int, holiday int)
$body$ 
LANGUAGE SQL;

2

0

slav 3 年前

我没有发言权,所以我留下了回复。