1

my previous question was marked as already answered. However the suggested solution (enter link description here) helped me only a little bit. I want to know, how I get a view into my table structure or how to use the view as a column. With help of the suggestted solution I have created the following statement:

CREATE VIEW my_col4 AS
SELECT transaction.quantity * transaction.unit_price AS total_price 
FROM transaction

Next step was to execute the sql-statment: SELECT * FROM my_col4 My result is 130.00 €

and this is the correct result of quantitiy(10) * unit_price(13.00)

But I don't know, how to implement this into my table, respectably how a further sql-statement should look like to get the following result:

|id  description  |  quantity  | unit_price  | total_price |
|1   Energy drink |  10        |     13.00   |    130.00   |

I hope my thoughts are correct and I'm looking forward to your answer.

Here is the sql-statement of my (shortened) table 

CREATE TABLE public.transaction3
(
    id integer NOT NULL,
    description character varying(50) COLLATE pg_catalog."default" NOT NULL,
    quantity real NOT NULL,
    unit_price money NOT NULL,
    total_price money NOT NULL,
    CONSTRAINT transaction3_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.transaction3
    OWNER to postgres;
S-Man
  • 22,521
  • 7
  • 40
  • 63
Sabine Sonnholtzer
  • 13
  • 3
  • Sabine could you provide us the criteria or condition to join `transtraction3` table record with a `my_col4` view record ? – Victor Sep 23 '18 at 12:51

1 Answers1

2

The items after SELECT are actually (scalar) expressions. These could be plain column names, or expressions referring to column names. They can also contain functions referring to the column names , or functions not referring to any column. (such as random() ornow() ) 

CREATE VIEW my_transaction AS
SELECT t.id , t.discription
        , t.quantity , t.unit_price
        , t.quantity * t.unit_price AS total_price
          -- nonsense below this line ...
        , upper(t.discription) AS "CAPITALS"
        , now() AS heute
        , (random()* 1000000)::integer AS lottery_number
FROM transaction t
    ;
wildplasser
  • 43,142
  • 8
  • 66
  • 109