в 13:45, 26 мая 2010

2010-05-26T13:45:56Z

Admin: 1 версия

2010-05-26T10:13:54Z

1 версия

Новая страница

==Create data type==



<source lang="sql">
postgres=#
postgres=#
postgres=# CREATE TYPE sum_prod AS (sum int, product int);
CREATE TYPE
postgres=#
postgres=# CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
postgres-# AS "SELECT $1 + $2, $1 * $2"
postgres-# LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# select sum_n_product (2, 1);
REATE
sum_n_product
---------------
(3,2)
(1 row)
postgres=#
postgres=# drop function sum_n_product (int, int) cascade;
DROP FUNCTION
postgres=# drop type sum_prod cascade;
DROP TYPE
postgres=#
postgres=#

</source>

==Create data type and use it as a column data type==



<source lang="sql">

postgres=# CREATE TYPE inventory_item AS (
postgres(# name text,
postgres(# supplier_id integer,
postgres(# price numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(# item inventory_item,
postgres(# count integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW("fuzzy dice", 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
item | count
------------------------+-------
("fuzzy dice",42,1.99) | 1000
(1 row)
postgres=#
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#
postgres=#

</source>

==Declaration of Composite Types==



<source lang="sql">
postgres=#
postgres=# -- Declaration of Composite Types
postgres=#
postgres=# CREATE TYPE complex AS (
postgres(# r double precision,
postgres(# i double precision
postgres(# );
CREATE TYPE
postgres=#
postgres=#
postgres=# drop type complex;
DROP TYPE
postgres=#

</source>

==Row function with defined data type==



<source lang="sql">

postgres=#
postgres=# CREATE TYPE inventory_item AS (
postgres(# name text,
postgres(# supplier_id integer,
postgres(# price numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(# item inventory_item,
postgres(# count integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW("fuzzy dice", 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
item | count
------------------------+-------
("fuzzy dice",42,1.99) | 1000
(1 row)
postgres=#
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#

</source>

==Using the %TYPE attribute==



<source lang="sql">

postgres=#
postgres=#
postgres=#
postgres=# CREATE TABLE "authors" (
postgres(# "id" integer NOT NULL,
postgres(# "last_name" text,
postgres(# "first_name" text,
postgres(# Constraint "authors_pkey" Primary Key ("id")
postgres(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into authors values (1111, "Martin", "Jason");
INSERT 0 1
postgres=# insert into authors values (1212, "Worsley", "Robert");
INSERT 0 1
postgres=# insert into authors values (15990, "Mathews", "John");
INSERT 0 1
postgres=# insert into authors values (25041, "Smith", "Williams");
INSERT 0 1
postgres=# insert into authors values (16, "Alcott", "May");
INSERT 0 1
postgres=# insert into authors values (4156, "King", "Stephen");
INSERT 0 1
postgres=# insert into authors values (1866, "Herbert", "Margaret");
INSERT 0 1
postgres=# insert into authors values (1644, "Hogarth", "Celia");
INSERT 0 1
postgres=# insert into authors values (2031, "Brown", "Wise");
INSERT 0 1
postgres=# insert into authors values (115, "Poe", "Allen");
INSERT 0 1
postgres=# insert into authors values (7805, "Lutz", "Mark");
INSERT 0 1
postgres=# insert into authors values (7806, "Rice", "Tom");
INSERT 0 1
postgres=# insert into authors values (1533, "Black", "Chris");
INSERT 0 1
postgres=# insert into authors values (1717, "Brite", "Linda");
INSERT 0 1
postgres=# insert into authors values (2112, "Larry", "Edward");
INSERT 0 1
postgres=# insert into authors values (2001, "Clarke", "Alison");
INSERT 0 1
postgres=# insert into authors values (1213, "Green", "Mary");
INSERT 0 1
postgres=#
postgres=# select * from authors;
id | last_name | first_name
-------+-----------+------------
1111 | Martin | Jason
1212 | Worsley | Robert
15990 | Mathews | John
25041 | Smith | Williams
16 | Alcott | May
4156 | King | Stephen
1866 | Herbert | Margaret
1644 | Hogarth | Celia
2031 | Brown | Wise
115 | Poe | Allen
7805 | Lutz | Mark
7806 | Rice | Tom
1533 | Black | Chris
1717 | Brite | Linda
2112 | Larry | Edward
2001 | Clarke | Alison
1213 | Green | Mary
(17 rows)
postgres=#
postgres=# drop function get_author(text);
DROP FUNCTION
postgres=#
postgres=# -- Using the %TYPE attribute
postgres=#
postgres=# CREATE FUNCTION get_author (text) RETURNS text AS "
postgres"# DECLARE
postgres"# -- Declare an alias for the function argument,
postgres"# -- which should be the first name of an author.
postgres"# f_name ALIAS FOR $1;
postgres"# l_name authors.last_name%TYPE;
postgres"# BEGIN
postgres"# SELECT INTO l_name last_name FROM authors WHERE first_name = f_name;
postgres"#
postgres"# return f_name || "" "" || l_name;
postgres"#
postgres"# END;
postgres"# " LANGUAGE "plpgsql";
CREATE FUNCTION
postgres=#
postgres=# -- Results of the get_author(?) function
postgres=#
postgres=# SELECT get_author("Jason");
get_author
--------------
Jason Martin
(1 row)
postgres=#
postgres=# drop table authors;
DROP TABLE
postgres=#
postgres=#

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

PostgreSQL/Postgre SQL/Type - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия