Re: select few fields as a single field

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