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');
                              ^

Continue Reading »