predavani parametru do funkce v sql (Postgres 7.3.2)
Jiri Chaloupka
dev na b2bexpander.com
Neděle Březen 16 13:02:27 CET 2003
Zdravim,
mam definovany typ:
create type t_dealerdd as (
id int4,
usr int4,
sells int4,
oksells int4,
failsells int4,
allpoints int4,
fapoints int4,
freepoints int4,
freefapoints int4);
pak mam funkci, ktera by mela vracet hodnotu vyse deklarovaneho typu:
CREATE or replace FUNCTION procdd2(integer) RETURNS t_dealerdd AS '
DECLARE
iddealer alias for $1;
result t_dealerdd%ROWTYPE;
c1_cursor refcursor;
BEGIN
open c1_cursor for execute ''select d.id, d.usr,
(select count(*) from sells where dealerid = d.id and imei
is not null) as sells,
(select count(*) from sells where dealerid = d.id and imei is not
null and stateid = 1) as oksells,
(select count(*) from sells where dealerid = d.id and imei is not
null and stateid not in (1,4)) as failsells,
(select sum(points) from sells where dealerid = d.id and imei is not
null and stateid = 1) as allpoints,
(select sum(points) from sells where dealerid = d.id and fanr is not
null) as fapoints,
(select
case
when u.usedpoints is not null then (sum(s.points) - u.usedpoints)
else sum(s.points)
end
from sells s left join view_dealerusedpoints u on u.dealerid =
s.dealerid
where s.imei is not null and s.stateid = 1 and s.dealerid = d.id
group by u.usedpoints) as freepoints,
(select
case
when u.usedpoints is not null then (sum(s.points) - u.usedpoints)
else sum(s.points)
end
from sells s left join view_dealerflusedpoints u on u.dealerid =
s.dealerid
where s.imei is null and s.stateid not in (5,6) and s.dealerid =
d.id group by u.usedpoints) as freefapoints
from dealer d
where d.id = '' || iddealer;
fetch c1_cursor into result;
close c1_cursor;
return result;
END;
' language 'plpgsql';
potud vse v poradku.
nicmene nakonec si nejsem jisty, jak se k vysledku dostat, neb teoreticky:
create or replace function procdd3(integer) returns setof t_dealerdd as
'select procdd2($1)' language 'sql';
narazi na problem jak do funkce procdd2() prenest v sql predavanou hodnotu.
Poradite?
Dik Jirka
Další informace o konferenci Databases