Re: select few fields as a single field

On Thu, 2002-01-10 at 12:10, Roman Gavrilov wrote:
> Ok I think after all I will use view.
> However i'll have to join many tables in the view want it slow the performance
> dramatically ?

You can consider a view as a stored query -when you refer to it, the
clauses from the view are "pulled up" into the query which refers to it.
In this way there's not a significant performance impact from using a
view (over using a simple query). If you need lots of joins for the
view, surely you need them for the query as well?

There is, as always, another way. If you were willing to add a fullname
column to your table, you could update this column using a trigger, viz:

create table sample (name text, version text, release text, fullname
text);

create function tf_fullname() returns opaque as '
begin
  new.fullname = new.name || ''-'' || new.version || ''-'' ||
new.release;
  return new;
end;
' language 'plpgsql';

create trigger trig_fullname before insert or update on sample for each
row execute procedure tf_fullname();

Then you can use:

workspace=# insert into sample values ('test',1,3);
INSERT 16583 1
workspace=# select fullname from sample;
 fullname
----------
 test-1-3
(1 row)

workspace=# update sample set version=1,release=4 where name='test';
UPDATE 1
workspace=# select fullname from sample;
 fullname
----------
 test-1-4
(1 row)


This will make sure that the fullname column is kept in sync with the
name, version and release (Note that with this trigger, there's no way
to set fullname to anything other than the concatenation above.)

Hope this helps.

Regards

John