psani funkce v pgsql

[Jiri Chaloupka]

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