PostgreSQL stored procedure using iBatis
The error occurred while applying a parameter map:
--- Check the newSubs-InlineParameterMap.
--- Check the statement (query failed).
--- Cause:
org.postgresql.util.PSQLException:
ERROR: wrong record type supplied in RETURN NEXT Where:
PL/pgSQL function "getnewsubs" line 34 at return next
the function detail is as below....
CREATE OR REPLACE FUNCTION getnewsubs(timestamp without time zone, timestamp without time zone, integer)
RETURNS SETOF record AS
$BODY$declare
v_fromdt alias for $1;
v_todt alias for $2;
v_domno alias for $3;
v_cursor refcursor;
v_rec record;
v_cpno bigint;
v_actno int;
v_actname varchar(50);
v_actid varchar(100);
v_cpntypeid varchar(100);
v_mrp double precision;
v_domname varchar(100);
v_usedt timestamp without time zone;
v_expirydt timestamp without time zone;
v_createdt timestamp without time zone;
v_ctno int;
v_phone varchar;
begin
open v_cursor for select cpno,c.actno,usedt from cpnusage c inner join account s on s.actno=c.actno where usedt >= $1 and usedt < $2 and validdomstat(s.domno,v_domno) order by c.usedt;
fetch v_cursor into v_cpno,v_actno,v_usedt;
while found
loop
if isactivation(v_cpno,v_actno,v_usedt) IS TRUE then
select into v_actno,v_actname,v_actid,v_cpntypeid,v_mrp,v_domname,v_ctno,v_cpno,v_usedt,v_expirydt,v_createdt,v_phone a.actno,a.actname as name,a.actid as actid,c.descr as cpntypeid,l.mrp as mrp,s.domname as domname,c.ctno as ctno,b.cpno,b.usedt,b.expirydt,d.createdt,a.phone from account a
inner join cpnusage b on a.actno=b.actno
inner join cpn d on b.cpno=d.cpno
inner join cpntype c on d.ctno=c.ctno
inner join ssgdom s on a.domno=s.domno
left join price_class l ON l.price_class_id=b.price_class_id
where validdomstat(a.domno,v_domno) and b.cpno=v_cpno and b.actno=v_actno;
select into v_rec v_actno,v_actname,v_actid,v_cpntypeid,v_mrp,v_domname,v_ctno,v_cpno,v_usedt,v_expirydt,v_createdt,v_phone;
return next v_rec;
end if;
fetch v_cursor into v_cpno,v_actno,v_usedt;
end loop;
return ;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION getnew开发者_C百科subs(timestamp without time zone, timestamp without time zone, integer) OWNER TO radius
If I run the function from the console it runs fine and gives the correct response. But when used through java it causes the above error.
Immediate cause of the error: The datatype of one or more these variables v_cpno,v_actno,v_usedt
does not match the corresponding columns in the tables cpnusage
and account
.
More generally, your plpgsql function is needlessly complicated and obscure. Could be written with a fraction of the code. I simplified to:
CREATE OR REPLACE FUNCTION getnewsubs(timestamp, timestamp, integer)
RETURNS TABLE (
actno int
,actname text
,actid text
,cpntypeid text
,mrp double precision
,domname text
,ctno int
,cpno bigint
,usedt timestamp without time zone
,expirydt timestamp without time zone
,createdt timestamp without time zone
,phone text) AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN
SELECT cpno, c.actno, usedt
FROM cpnusage c
JOIN account s ON s.actno = c.actno
WHERE usedt >= $1
AND usedt < $2
AND validdomstat(s.domno, $3)
ORDER BY c.usedt;
LOOP
RETURN QUERY
SELECT a.actno
,a.actname
,a.actid
,c.descr -- as cpntypeid
,l.mrp
,s.domname
,c.ctno
,b.cpno
,b.usedt
,b.expirydt
,d.createdt
,a.phone
FROM account a
JOIN cpnusage b ON a.actno = b.actno
JOIN cpn d ON b.cpno = d.cpno
JOIN cpntype c ON d.ctno = c.ctno
JOIN ssgdom s ON a.domno = s.domno
LEFT JOIN price_class l ON l.price_class_id = b.price_class_id
WHERE validdomstat(a.domno, $3)
AND b.cpno = r.cpno
AND b.actno = r.actno;
AND isactivation(r.cpno,r.actno,r.usedt) IS TRUE
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION getnewsubs(timestamp without time zone, timestamp without time zone, integer) OWNER TO radius
Could probably be simplified to a single RETURN QUERY
. But I have beaten this dead horse quite enough.
精彩评论