I just forgot to add that and activity after that variable.

Details, details, details... :)

The query might be better as a join:

select distinct
    foo.id        key,
    last_value(data.value)
     over
     (
      partition by
        data.foo_id
      order by
        data.id
     )            value
from
    foo,
    foo_data    data
where
        foo.id        <> ?
  and    foo.activity    is true
        —
  and    data.foo_id    = foo.id
  and    data.aktivity    is true
  and    data.key        = 'name';
[download]

It's just natural derivation after associative arrays and key-value

That is great for programming. In the database world, it is generally considered the wrong approach, when the data is known. If data captured is unknown, it is likely the only choice.

Proper indexing is essential in that key-value-case.

That will work on small tables. Once it gets large, the index is of little help. Partitioned tables is one way out. In any case, EAVs are best avoided.

put everything to db, even code.

People do that, but it is error prone and confusing. Besides, queries can be stored natively in the database as a view or inside a stored procedure. That is what they are there for.

Join might be better than subquery. And as far as I can see, that data.key can be multiple with ors or even in and like/ilike should work also.

I have tables empty (=just couple of lines) and subquery is faster than window-join. 1.1ms vs 1.4ms. No indexes. Postresql v8.4.22, Debian 6.0.10

Usually there should be vacuuming of some kind. Just transfer old to history.

Do you have any projects in mind, where Perl-code is stored in db?

There is ofc limit how much views etc one should put to db. Sometimes it's better to do with code than in db.
Views are nice, when you want to limit permission of some user. There are few things in on update and on delete.

If the query is faster. so be it. IMO, the join is clearer even at the cost of .3 milliseconds. :)

Usually there should be vacuuming of some kind. Just transfer old to history.

Vacuuming is an admission of defeat. I cringe when i see "history" tables. The best way is to have a column mentioning status. "Active" flags are a step in the right direction, but would be better as a date recording the removal, a name of who removed it, a reason of why, the id of what superseded it, etc. That acts as both a flag and gives some useful information. Even better would be to include a status fked to a status table, which can included more statuses as time goes on. After that, a good index helps with speed.

Do you have any projects in mind, where Perl-code is stored in db?

No. Just seen it before. Last case i saw was tcl. Made distribution and setup easy, however.

There is ofc limit how much views etc one should put to db.

Why? Postgresql does not list any such limit. And, as views are pseudo objects used to store queries, define business objects, and help with columnar-rights, there actually should be--when the project calls for it--a plethora a views. A good naming scheme could easily keep everything in order.

Sometimes it's better to do with code than in db.

Formatting and processing, yes. Querying and permissions, no. The database handles accessing and retrieving the data, the ui displays it to the user.

Views are nice, when you want to limit permission of some user.

To be clear, they are nice when you want to limit the data the user sees. Permissions are already handled natively.