- Code/Resource
- Windows Develop
- Linux-Unix program
- Internet-Socket-Network
- Web Server
- Browser Client
- Ftp Server
- Ftp Client
- Browser Plugins
- Proxy Server
- Email Server
- Email Client
- WEB Mail
- Firewall-Security
- Telnet Server
- Telnet Client
- ICQ-IM-Chat
- Search Engine
- Sniffer Package capture
- Remote Control
- xml-soap-webservice
- P2P
- WEB(ASP,PHP,...)
- TCP/IP Stack
- SNMP
- Grid Computing
- SilverLight
- DNS
- Cluster Service
- Network Security
- Communication-Mobile
- Game Program
- Editor
- Multimedia program
- Graph program
- Compiler program
- Compress-Decompress algrithms
- Crypt_Decrypt algrithms
- Mathimatics-Numerical algorithms
- MultiLanguage
- Disk/Storage
- Java Develop
- assembly language
- Applications
- Other systems
- Database system
- Embeded-SCM Develop
- FlashMX/Flex
- source in ebook
- Delphi VCL
- OS Develop
- MiddleWare
- MPI
- MacOS develop
- LabView
- ELanguage
- Software/Tools
- E-Books
- Artical/Document
advanced.source
Package: postgresql-6.5.2.tar.gz [view]
Upload User: blenddy
Upload Date: 2007-01-07
Package Size: 6495k
Code Size: 3k
Category:
Database system
Development Platform:
Unix_Linux
- ---------------------------------------------------------------------------
- --
- -- advanced.sql-
- -- more POSTGRES SQL features. (These are not part of the SQL-92
- -- standard.)
- --
- --
- -- Copyright (c) 1994, Regents of the University of California
- --
- -- $Id: advanced.source,v 1.3 1999/07/08 15:28:51 momjian Exp $
- --
- ---------------------------------------------------------------------------
- -----------------------------
- -- Inheritance:
- -- a table can inherit from zero or more tables. A query can reference
- -- either all rows of a table or all rows of a table plus all of its
- -- descendants.
- -----------------------------
- -- For example, the capitals table inherits from cities table. (It inherits
- -- all data fields from cities.)
- CREATE TABLE cities (
- name text,
- population float8,
- altitude int -- (in ft)
- );
- CREATE TABLE capitals (
- state char(2)
- ) INHERITS (cities);
- -- now, let's populate the tables
- INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
- INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
- INSERT INTO cities VALUES ('Mariposa', 1200, 1953);
- INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
- INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');
- SELECT * FROM cities;
- SELECT * FROM capitals;
- -- like before, a regular query references rows of the base table only
- SELECT name, altitude
- FROM cities
- WHERE altitude > 500;
- -- on the other hand, you can find all cities, including capitals, that
- -- are located at an altitude of 500 'ft or higher by:
- SELECT c.name, c.altitude
- FROM cities* c
- WHERE c.altitude > 500;
- -----------------------------
- -- Time Travel:
- -- this feature allows you to run historical queries.
- -- removed for v6.3, but possible using triggers.
- -- see contrib/spi/README for more information.
- -----------------------------
- -- first, let's make some changes to the cities table (suppose Mariposa's
- -- population grows 10% this year)
- -- UPDATE cities
- -- SET population = population * 1.1
- -- WHERE name = 'Mariposa';
- -- the default time is the current time ('now'):
- -- SELECT * FROM cities WHERE name = 'Mariposa';
- -- we can also retrieve the population of Mariposa ever has. ('epoch' is the
- -- earliest time representable by the system)
- -- SELECT name, population
- -- FROM cities['epoch', 'now'] -- can be abbreviated to cities[,]
- -- WHERE name = 'Mariposa';
- ----------------------
- -- Arrays:
- -- attributes can be arrays of base types or user-defined types
- ----------------------
- CREATE TABLE sal_emp (
- name text,
- pay_by_quarter int4[],
- schedule text[][]
- );
- -- insert instances with array attributes. Note the use of braces
- INSERT INTO sal_emp VALUES (
- 'Bill',
- '{10000,10000,10000,10000}',
- '{{"meeting", "lunch"}, {}}');
- INSERT INTO sal_emp VALUES (
- 'Carol',
- '{20000,25000,25000,25000}',
- '{{"talk", "consult"}, {"meeting"}}');
- ----------------------
- -- queries on array attributes
- ----------------------
- SELECT name FROM sal_emp WHERE
- sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2];
- -- retrieve third quarter pay of all employees
- SELECT sal_emp.pay_by_quarter[3] FROM sal_emp;
- -- select subarrays
- SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE
- sal_emp.name = 'Bill';
- -- clean up (you must remove the children first)
- DROP TABLE sal_emp;
- DROP TABLE capitals;
- DROP TABLE cities;