Re: select few fields as a single field

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