RE: [GENERAL] How to do this in Postgres

> Hi there,
>
> I tried all I could think of with the following problem, perhaps
> someone has another idea.
>
> I have a table where for each id there may (and often are) multiple
> rows with some kind of priority.
> create table data ( id1 int4, id2 int4, <<lots of data>>,
> prio int4 );
> The minimal priority is not guaranteed to be 1. There are 200k
> different ids with up to 10 entries, summing up to 400k rows.
>
> Not I want to do something like this:
>
> select * from data where <<prio is minimal per id pair>>.
>
> First attempt (deleting non minimal)
> ------------------------------------
>
> select a.id1, a.id2, a.prio
> into bugos
> from a data, b data
> where a.prio > b.prio and a.id1 = b.id1 and a.id2 = b.id2;
>
> delete from data
> where id1 = bogus.id1 and id2 = bogus.id2 and prio = bogus.prio;
>
> The join does not seem to complete. I am not sure whether I should
> have waited longer, but after 4h without significant disk access I
> do not think that this thing will ever return. Indexing didn't help.
>
> Second attempt (stored procedures)
> ----------------------------------
>
> create function GetData( int4, int4 )
> returns data
> as 'select *
> from data
> where id1 = $1 and id2 = $2
> order by prio
> limit 1'
> language 'sql';
>
> select GetData(id1,id2) from <<table with unique ids>>;
>
> limit in functions is not yet implemented in postgres (6.5.2)
>
> Third attempt (use perl on dumped table)
> ----------------------------------------
>
> I don't want to :-)

IMHO, Perl is the solution,
some foreach architecture on @table should work...
Of course it could be ordinal (usable on fixed row system).
The best would be to recover row name as well(if someone has an
idea to do this ???):

foreach $item in fetched_row{
#Recover row name (Process runned once (rec#1)
???
#Recover row content
}

Regards, Fabian