PostgreSQL

Скачать в pdf «PostgreSQL»

test=> CREATE TABLE part (


test(>


part id


integer,


test(>


name


CHAR(30),


test(>


cost


NUMERIC(8,2),


test(>


weight


FLOAT


test(> );


CREATE


test=> INSERT INTO part VALUES (637, ‘cable’, 14.29, 5);


INSERT 20867 1


test=> INSERT INTO part VALUES (638, ‘sticker’, 0.84, 1);


INSERT 20868 1


test=> INSERT INTO part VALUES (639, ‘bulb’, 3.68, 3);


INSERT 20869 1


test=> SELECT part_id,


test->    name,


test->    cost,


test->    tax(cost),


test->    cost + tax(cost) AS total


test-> FROM part


test-> ORDER BY part_id;


part_id |    name    | cost | tax | total


———+———————————+——-+——+——


| 14.29 | 0.86 | 15.15 | 0.84 | 0.05 | 0.89 | 3.68 | 0.22 | 3.90


637    | cable


638    | sticker


639    | bulb (3 rows)


test=> CREATE FUNCTION shipping(numeric) test-> RETURNS numeric test-> AS ‘SELECT CASE


test’>    WHEN    $1    <    2    THEN    CAST(3.00    AS    numeric(8,2))


test’>    WHEN    $1    >=    2    AND $1 < 4    THEN    CAST(5.00    AS    numeric(8,2))


test’>    WHEN    $1    >=    4    THEN    CAST(6.00    AS    numeric(8,2))


test’>    END;’


test-> LANGUAGE ‘sql’;


CREATE


test=> SELECT part_id,


test->    trim(name) AS name,


test->    cost,


test->    tax(cost),


test->    cost + tax(cost)    AS    subtotal,


test->    shipping(weight),


test->    cost + tax(cost)    +    shipping(weight) AS total


test-> FROM part test-> ORDER BY part_id;


:_id | —-+-


name | ———+-


cost | ——+-


tax |


subtotal | ———+-


shipping |


total


637 |


cable |


14.29 |

Скачать в pdf «PostgreSQL»