Hi
I had to write big sql code returning max 10 rows (3 selects 7,10 and 13
joins,
mostly equi-joins - too long to include). I tried do solve this in
different ways:
1. create view
Look at simple test.sql in attachement. Just make psql -f test.sql. I
have PostgreSQL 7.1.2.
I'm used to C,C++, not to SQL and I can't understand why selecting 1 row
from K2 doesn't use index scan for only 1 row.
-> Index Scan using k_pkey on k k1 (cost=0.00..2.02 rows=1
width=8)
-> Index Scan using k_pkey on k k2 (cost=0.00..8.14 rows=10 width=8)
Second view - bla2 works much worse...
Does it correspond to my question about dynamic date which should be
static (pgsql-bugs)?
Views would be nice but only if we could send them params...
2. sql function
I tried to return _int4 (array of int) - enough for me, but...
how to make array of non-static int?
'{0,1,2}' works fine, but '{id_t,id_k}' not
3. pl/pgsql function
first step - create temp table (once per session)
second step - pl/pgsql clears and inserts into that table.
I think it's the best solution. It works really fast. I can split sql
into smaller pieces and work on them separately.
Does anyone know how to make function returning rows another way?
Regards,
Tomekcreate table T(
id_t integer not null PRIMARY KEY,
nazwa varchar (80)
);
create table K(
id_k integer not null,
id_t integer not null references T,
data_k date not null,
primary key(id_t,data_k)
);
create table TP(
id_t integer not null references T,
nr smallint not null,
date_diff smallint not null default 0
);
create index ind_tp_t on TP(id_t);
insert into T values (1,'aa');
insert into T values (2,'bb');
insert into K values (1,1,'2001-09-12');
insert into K values (2,1,'2001-09-13');
insert into K values (3,1,'2001-09-14');
insert into K values (4,1,'2001-09-15');
insert into K values (5,2,'2001-09-12');
insert into K values (6,2,'2001-09-13');
insert into K values (7,2,'2001-09-14');
insert into K values (8,2,'2001-09-15');
insert into K values (9,2,'2001-09-16');
insert into TP values (1,1,0);
insert into TP values (2,3,0);
create view bla as select
T1.id_t,
K1.data_k+TP.date_diff as data1,
TP.nr
from
(select * from T limit 1) T1
inner join TP on (TP.id_t=T1.id_t)
inner join K K1 on (TP.id_t=K1.id_t and K1.data_k='2001-09-23')
inner join K K2 on (TP.id_t=K2.id_t and K2.data_k=K1.data_k+TP.date_diff);
create view bla2 as select
T1.id_t,
K1.data_k+TP.date_diff as data1,
TP.nr
from
(select * from T limit 1) T1
inner join TP on (TP.id_t=T1.id_t)
inner join K K1 on (TP.id_t=K1.id_t) -- and K1.data_k='2001-09-23')
inner join K K2 on (TP.id_t=K2.id_t and K2.data_k=K1.data_k+TP.date_diff);
select * from pg_indexes where tablename in ('t','k','tp');
explain select * from bla where id_t=1;
explain select * from bla2 where id_t=1 and data1='2001-09-24';
drop view bla2;
drop view bla;
drop table TP;
drop table K;
drop table T;