psani funkce v pgsql
Jiri Chaloupka
dev na b2bexpander.com
Úterý Prosinec 3 12:31:42 CET 2002
Takto to funguje:
z procedury:
create temporary table temptestdd as select d.id, d.usr,
(select 1) as sells,
(select 2) as oksells,
(select 3) as failsells,
(select 4) as allpoints,
(select 5) as fapoints
from dealer d
where d.id = iddealer;
db=# select testdd(2587);
testdd
--------
t
(1 row)
db=# select * from temptestdd;
id | usr | sells | oksells | failsells | allpoints | fapoints
------+------+-------+---------+-----------+-----------+----------
2587 | 1529 | 1 | 2 | 3 | 4 | 5
(1 row)
Navic se problem vyskytuje jen pri pouziti procedury, kdyz to udelam na
urovni db, je to v poradku:
db=# create temporary table tempdd as select d.id, d.usr,
db-# (select count(*) from sells where dealerid = d.id and imei
!= '') as sells,
db-# (select count(*) from sells where dealerid = d.id and imei
!= '' and stateid = 1) as oksells,
db-# (select count(*) from sells where dealerid = d.id and imei
!= '' and stateid not in (1,4)) as failsells,
db-# (select sum(points) from sells where dealerid = d.id and
imei != '' and stateid = 1) as allpoints,
db-# (select sum(points) from sells where dealerid = d.id and
fanr > 0) as fapoints
db-# from dealer d
db-# where d.id = 2587;
SELECT
db=# select * from tempdd;
id | usr | sells | oksells | failsells | allpoints | fapoints
------+------+-------+---------+-----------+-----------+----------
2587 | 1529 | 355 | 264 | 91 | 353 |
(1 row)
Jeste pro upresneni, byt to asi nebude mit vyznam, testuji to na Postgres
7.2.2, v ostrem to bude na 7.1.3
Jirka
<citováno kdo="Karel Zak">
> On Tue, Dec 03, 2002 at 11:44:12AM +0100, Jiri Chaloupka wrote:
>> ale kdyz pouziju proceduru, nejsou tam vsechny sloupce, navic nesedi
>> vysledek:
>
> Ten pocet slpoupcu je divny. Co se stane pokud tam v tech
> subselectech bude jen "(select 1) as neco". Budou tam pak vsechny
> sloupce?
>
> Karel
>
> --
> Karel Zak <zakkr na zf.jcu.cz>
> http://home.zf.jcu.cz/~zakkr/
--
Jiri Chaloupka
B2BExpander.com
chaloupka na b2bexpander.com
**********************************************
http://www.b2bexpander.com/
http://www.chalu.cz - intranet grouprare solution
Další informace o konferenci Databases