Я борюсь с как вызвать функцию хранимой процедуры PostgreSQL с передачей параметра.Я не знаю, и я ошибся с передачей параметра вызова функции. Пожалуйста, посоветуйте мне.
Это мой php файл calling.php
include('connection.php');
echo $hud='15';
echo $phc='80001';
echo $Firstdate='2015-08-01';
echo $Seconddate='2015-10-01';
echo $Todate='2015-10-31';
$dvn_sql =<<<EOF
select * from prisonparam($hud text
,$phc text
,$Firstdate Date
,$Seconddate Date
,$Todate Date);
EOF;
$dvn_ret = pg_query($db, $dvn_sql);
while($row = pg_fetch_array($dvn_ret))
{
echo $ben_st=trim($row['sc']);
echo $round=trim($row['scupto']);
}
И это мои функции хранимых процедур
CREATE OR REPLACE FUNCTION prisonparam(dvn_cd text
,phc_cd text
,Firstdate Date
,Seconddate Date
,Todate Date)
RETURNS table (round text,sc bigint,scupto bigint)
AS $$
WITH a
AS (
SELECT round AS round
,Sum(ben_sc) AS SC
FROM prison_issue
WHERE (
DATE BETWEEN Firstdate
AND Todate
)
AND dvn_cd = dvn_cd
AND phc_cd = phc_cd
GROUP BY round ORDER BY round
)
,b
AS (
SELECT round AS round_up
,Sum(ben_sc) AS SC_up
FROM prison_issue
WHERE (
DATE BETWEEN Seconddate
AND Todate
)
AND dvn_cd = dvn_cd
AND phc_cd = phc_cd
GROUP BY round ORDER BY round
)
SELECT b.round_up AS round
,coalesce(a.sc, 0) AS SC
,coalesce(b.sc_up, 0) AS SCUPTO
FROM a
RIGHT JOIN b ON a.round = b.round_up
$$ LANGUAGE sql;
ошибка
Warning: pg_query(): Query failed:
ERROR: function prisonparam(integer, integer, integer, integer, integer) does not exist
LINE 1: select * from prisonparam(15,80001, 2015-08-01,2015-10... ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
in C:\wamp\www\mhp\test.php on line 12
Попробуйте заменить запрос
select * from prisonparam($hud text
,$phc text
,$Firstdate Date
,$Seconddate Date
,$Todate Date);
с
select * from prisonparam('$hud','$phc','$Firstdate','$Seconddate' ,'$Todate');
или же
$dvn_sql ="select * from prisonparam('".$hud."','".$phc."','".$Firstdate."','".$Seconddate."','".$Todate."')";
Других решений пока нет …