pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomasz Myrta <jasiek@lamer.pl>
To: pgsql-sql@postgresql.org
Subject: functions returning
Date: Mon, 17 Sep 2001 10:56:08 +0200
Message-ID: <3BA5BAA8.18545A8@lamer.pl> (raw)

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,
Tomek

Attachments:

test.sql (text/plain, 1.8K) download create 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;

Message-ID: <3BA5BAA8.18545A8@lamer.pl>
Permalink:  ../3BA5BAA8.18545A8@lamer.pl/
Also on:    postgresql.org/message-id/3BA5BAA8.18545A8@lamer.pl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jasiek@lamer.pl
  Subject: Re: functions returning
  In-Reply-To: <3BA5BAA8.18545A8@lamer.pl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox