- 论坛徽章:
- 0
|
建立user表后查询数据的问题
原帖由 "nettoobad" 发表:
数据库开发的原则之一:
建表最好有表前缀
比如: mypro_user等,比较不容易和系统表冲突.
pgsql 有schema 用不着前缀.
mypro.user 不是更好吗??
在两个schema下可以的相同的.表,过程,解发器,view...............
===============================================
CREATE SCHEMA btob;
-- ======================================================================
-- 'btob.directory'
-- ======================================================================
Drop table btob.directory CASCADE;
Create table btob.directory
(
"id" Serial NOT NULL,
"parent" Integer NOT NULL Default 0,
"name" Varchar(20)NOT NULL ,
"status" boolean Default 'true',
"description" Varchar(50),
"created" Timestamp Default current_timestamp::timestamp (0) without time zone,
"modified" Timestamp Default current_timestamp::timestamp (0) without time zone,
UNIQUE (id,parent,name),
PRIMARY KEY ("id"
-- FOREIGN KEY (parent) REFERENCES directory (id) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO btob.directory (id,parent,name) VALUES (0,0,'/');
Alter table btob.directory add FOREIGN KEY (parent) REFERENCES btob.directory (id) ON UPDATE CASCADE ON DELETE CASCADE;
Create index "directory_index" on btob.directory using btree ("id","parent","name"
select * from directory;
-- ------------------------------------------------------
-- 'vdirectory'
-- ------------------------------------------------------
drop view btob.vdirectory ;
CREATE OR REPLACE VIEW btob.vdirectory AS
SELECT d.id,d.parent,d.name,d.status,d.description
-- CASE WHEN d.name='/' THEN '根目录' ELSE d.name END as name,
FROM btob.directory d
Where id >;0; |
|