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