Thread: select few fields as a single field

Hello everybody,
I have this question,
Suppose that I have a table with 3 fields name, version, release.
name | version | release
------------------
test   |  1           | 2
ema  |   1.2       | 2.2
------------------

I want to retrieve full name as 'select name || '-' || version || '-' release from table';
test-1-2
ema-1.2-2.2

I can do this as regular sql query;
But i would like to do this as 'select full_name from table'

One way is to create view which will do the job.
Other way to do this is to create additional field name full_name and to store the full name inside the field.
Is there any possibility to create function or constraint trigger that will know that when I am doing select full_name it should
concat name version release and return it as full_name.(full_name is virtual field)
I don't want to create it as view;
------------------------

Thanks
 
 

-- 
-----------------------------------------------------------------------------
 Roman Gavrilov                           
 Aduva Inc., Web Development Services.    
 work +972-3-7534324 mobile +972-54-834668
 romio@aduva.com, romio@netvision.net.il

 

On Thu, Jan 10, 2002 at 11:50:08AM +0200, Roman Gavrilov wrote:
> Suppose that I have a table with 3 fields name, version, release.
> name | version | release
> ------------------
> test   |  1           | 2
> ema  |   1.2       | 2.2
> ------------------
>
> I want to retrieve full name as 'select name || '-' || version || '-'
> release from table';
> test-1-2
> ema-1.2-2.2
>
> I can do this as regular sql query;
> But i would like to do this as 'select full_name from table'
>
> One way is to create view which will do the job.
> Other way to do this is to create additional field name full_name and to
> store the full name inside the field.
> Is there any possibility to create function or constraint trigger that
> will know that when I am doing select full_name it should
> concat name version release and return it as full_name.(full_name is
> virtual field)

Not a trigger, triggers work only ON UPDATE and ON INSERT but not ON SELECT.

> I don't want to create it as view;

That's they way how PostgreSQL does this kind of work. Why not ?

Alternatively you can use a function:

  SELECT fullname(table) FROM table;

Here's the function definition:

  CREATE OR REPLACE FUNCTION fullname(table)
    RETURNS text AS
    'BEGIN
    RETURN $1.name || ''-'' || $1.version || ''-'' $1.release;
     END'
     LANGUAGE plpgsql;

The syntax of the CREATE FUNCTION statement as given here is valid for
PostgreSQL 7.2, former versions have a slightly different syntax. See the
command reference page for CREATE FUNCTION.

Good luck !

--
Holger Krug
hkrug@rationalizer.com

Thank you for your response.
Yes I know that i can make a function.
Is there a way to do this transparently I want to treat this function as a field in the table.
The reason why i want to do that is because i have a class that has generic method to retrieve data.
It receives list of field names and returns their values from the table.
If I want to implement the function method i will have to modify the class method and will have to treat specially the
retrieve fullname thing.
 
 

Holger Krug wrote:

On Thu, Jan 10, 2002 at 11:50:08AM +0200, Roman Gavrilov wrote:
> Suppose that I have a table with 3 fields name, version, release.
> name | version | release
> ------------------
> test   |  1           | 2
> ema  |   1.2       | 2.2
> ------------------
>
> I want to retrieve full name as 'select name || '-' || version || '-'
> release from table';
> test-1-2
> ema-1.2-2.2
>
> I can do this as regular sql query;
> But i would like to do this as 'select full_name from table'
>
> One way is to create view which will do the job.
> Other way to do this is to create additional field name full_name and to
> store the full name inside the field.
> Is there any possibility to create function or constraint trigger that
> will know that when I am doing select full_name it should
> concat name version release and return it as full_name.(full_name is
> virtual field)

Not a trigger, triggers work only ON UPDATE and ON INSERT but not ON SELECT.

> I don't want to create it as view;

That's they way how PostgreSQL does this kind of work. Why not ?

Alternatively you can use a function:

  SELECT fullname(table) FROM table;

Here's the function definition:

  CREATE OR REPLACE FUNCTION fullname(table)
    RETURNS text AS
    'BEGIN
        RETURN $1.name || ''-'' || $1.version || ''-'' $1.release;
     END'
     LANGUAGE plpgsql;

The syntax of the CREATE FUNCTION statement as given here is valid for
PostgreSQL 7.2, former versions have a slightly different syntax. See the
command reference page for CREATE FUNCTION.

Good luck !

--
Holger Krug
hkrug@rationalizer.com

-- 
-----------------------------------------------------------------------------
 Roman Gavrilov                           
 Aduva Inc., Web Development Services.    
 work +972-3-7534324 mobile +972-54-834668
 romio@aduva.com, romio@netvision.net.il

 

On Thu, Jan 10, 2002 at 12:58:18PM +0200, Roman Gavrilov wrote:
> Thank you for your response.
> Yes I know that i can make a function.
> Is there a way to do this transparently I want to treat this function as a
> field in the table.

No.

> The reason why i want to do that is because i have a class that has generic
> method to retrieve data.
> It receives list of field names and returns their values from the table.
> If I want to implement the function method i will have to modify the class
> method and will have to treat specially the
> retrieve fullname thing.

OK. But why not views ? That's exactly the use case where views should
be used in PostgreSQL. Using views you need not modify anything in
your class.

Alternative solution:

Change your class (if possible) to have the members name, version,
release and add a method getFullName() to the class. (I would never
do this but use views.)

--
Holger Krug
hkrug@rationalizer.com

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 ?
I run explain on the view and got
explain select event_id from get_event;
NOTICE:  QUERY PLAN:
Merge Join  (cost=376812.28..389383.78 rows=10000000 width=60)
when the biggest table contains 40 records only

when explain select event_id from intelligence;  :
NOTICE:  QUERY PLAN:
Seq Scan on intelligence  (cost=0.00..20.00 rows=1000 width=4)
 

Holger Krug wrote:

On Thu, Jan 10, 2002 at 12:58:18PM +0200, Roman Gavrilov wrote:
> Thank you for your response.
> Yes I know that i can make a function.
> Is there a way to do this transparently I want to treat this function as a
> field in the table.

No.

> The reason why i want to do that is because i have a class that has generic
> method to retrieve data.
> It receives list of field names and returns their values from the table.
> If I want to implement the function method i will have to modify the class
> method and will have to treat specially the
> retrieve fullname thing.

OK. But why not views ? That's exactly the use case where views should
be used in PostgreSQL. Using views you need not modify anything in
your class.

Alternative solution:

Change your class (if possible) to have the members name, version,
release and add a method getFullName() to the class. (I would never
do this but use views.)

--
Holger Krug
hkrug@rationalizer.com

-- 
-----------------------------------------------------------------------------
 Roman Gavrilov                           
 Aduva Inc., Web Development Services.    
 work +972-3-7534324 mobile +972-54-834668
 romio@aduva.com, romio@netvision.net.il

 

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 ?
> I run explain on the view and got
> explain select event_id from get_event;
> NOTICE:  QUERY PLAN:
> Merge Join  (cost=376812.28..389383.78 rows=10000000 width=60)
> when the biggest table contains 40 records only
>
> when explain select event_id from intelligence;  :
> NOTICE:  QUERY PLAN:
> Seq Scan on intelligence  (cost=0.00..20.00 rows=1000 width=4)


How acurate are these numbers for the amount of returned rows? Could you
run a VACUUM ANALYZE and try again?

Jochem

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