Enter the terms you wish to search for.

Creating Virtual Columns

April 01, 2013

Databases store data in table columns, but sometimes it is useful to create non-storage columns for code clarity. For example, you might need to access a combination of columns frequently and don't want to repeatedly perform the combining operation in your application. In this case, a column can be created that acts as a virtual read-only column. In the example below, a fullname function is created that combines the firstname and lastname columns, and the fullname function is then accessed as a virtual column:

 

 

CREATE TABLE customer (id SERIAL, firstname TEXT, lastname TEXT);
 
-- create virtual column
CREATE FUNCTION fullname(customer) RETURNS text AS $$
    SELECT $1.firstname || ' ' || $1.lastname
$$ LANGUAGE SQL;
 
INSERT INTO customer VALUES (DEFAULT, 'Mark', 'Pennypincher');
 
SELECT customer.fullname FROM customer;
     fullname
-------------------
 Mark Pennypincher
(1 row)
 
-- the table name is not optional
SELECT fullname FROM customer;
ERROR:  column "fullname" does not exist
LINE 1: SELECT fullname FROM customer;
               ^
 
-- virtual columns are read-only
INSERT INTO customer (fullname) VALUES ('Andy Toll');
ERROR:  column "fullname" of relation "customer" does not exist
LINE 1: insert into customer (fullname) VALUES ('Andy Toll');
                              ^

 

 

You can even create column aliases in this way:

 

 

-- create column alias
CREATE FUNCTION nickname(customer) RETURNS text AS $$
    SELECT $1.firstname
$$ LANGUAGE SQL;
 
SELECT customer.nickname FROM customer;
 nickname
----------
 Mark
(1 row)

 

 

Only functions with a table name argument can be called in this way, i.e. as table_name.function_name. Because sql functions are often inlined before execution, their overhead is minimal — notice that the explain plan for the real and virtual column references are identical:

 

 

EXPLAIN VERBOSE SELECT customer.firstname, customer.nickname FROM customer;
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on public.customer  (cost=0.00..18.30 rows=830 width=32)
   Output: firstname, firstname
(2 rows)

 

 

Such functions can also be created in other server-side languages, like PL/pgSQL:

 

 

CREATE FUNCTION fullname2(customer) RETURNS text AS $$
BEGIN
        RETURN $1.firstname || ' ' || $1.lastname;
END
$$ LANGUAGE plpgsql;
 
SELECT customer.fullname2 FROM customer;
     nickname2
-------------------
 Mark Pennypincher
(1 row)

 

 

Of course, much of this can already be done with views:

 

 

CREATE VIEW customer_view AS 
    SELECT firstname, lastname, firstname || ' ' || lastname AS fullname 
    FROM customer;
 
SELECT fullname FROM customer_view;
     fullname
-------------------
 Mark Pennypincher
(1 row)

 

 

Views are nice in that they do not require creation of a function to use them, but they do require applications to reference the view name rather than the base table name. (Of course, ddl can be added to make views updatable.) Virtual columns are nice because they are only active when the virtual column is referenced on the base table.

Share this