Hi ,
I have a problem regarding stored procedure, it always informs me that there
is ERROR: mismatched parentheses after executing, If anyone can take a look
to see what i'm doing wrong :
create or replace function test(TEXT, INTEGER, TEXT, TEXT) returns refcursor
AS '
DECLARE
refCur REFCURSOR;
recOrdOne RECORD;
BEGINA
OPEN refCur FOR
SELECT fltime, network, port, tooctets, fromoctets,
sum(tooctets + fromoctets) AS sumoctets
FROM (SELECT distinct tariffs.port AS
port FROM tariffs WHERE tariffs.custid = $2 ) AS keys
LEFT OUTER JOIN (
SELECT fltime, network, tariffs.port AS port, sum(fld_tooctets) AS
tooctets, sum(fld_fromoctets) AS fromoctets, sum(fld_tooctets +
fld_fromoctets) AS sumoctets
FROM day_log, networks, tariffs, services
WHERE networks.custid = $2
AND day_log.custid = $2
AND tariffs.custid = $2
AND tariffs.tariff = networks.tariff
AND (FOR recOrdOne IN SELECT distinct fltime FROM day_log WHERE fltime
>= $3 AND fltime < $4 LOOP
IF recOrdOne .fltime = $3 THEN
fltime BETWEEN $3 AND to_date(date recOrdOne.fltime +
interval ''24 hours'', '' YYYY-MM-DD'')
ELSE
OR fltime BETWEEN recOrdOne.fltime AND to_date(date recOrdOne.fltime
+ interval ''24 hours'', '' YYYY-MM-DD'')
END IF;
END LOOP;)
AND fldestpeeraddress = network
AND fldestpeeraddress = $1
AND sourceaddr = ''f''
AND services.custid= $2
AND services.service = tariffs.port
AND flsourcetranstype = services.protocol
GROUP BY network, tariffs.port, fltime
) AS subs
USING(port)
GROUP BY network, port, tooctets, fromoctets, sumoctets, fltime
ORDER BY network, fltime, port;
RETURN refCur;
END;
' LANGUAGE 'plpgsql';
After i follow a procedure of executing,
begin;
select test('192.168.175.128/28', 70, '2004-01-10', '2004-02-10');
then i got following message
ERROR: mismatched parentheses
thx
