functions returning rows - Mailing list pgsql-general

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;