一直想为 pg 做点贡献，今天终于有空了， 于是写一个Postgresql 9.13 入门的教程 ...
部署上可以移步这里 ...
php 5.4.10 + nginx1.0.12 + PostgreSQL 9.1.3 源码编译自动化部署第二版
http://www.jb51.cc/article/p-aotagsej-hb.html
-----------------------------------------------------------------------------------------
| System | CentOS 5.7
-----------------------------------------------------------------------------------------
| DB | Postgresql 9.13
-----------------------------------------------------------------------------------------
lnpp脚本里面已经做了些初始化的工作，例如：
su postgres -c "$PG_ROOT/bin/initdb -D $PG_ROOT/data && exit"
我们先输入一些数据以供后面查询(详见后面补充）
-- Database: bpsimple -- DROP DATABASE bpsimple; CREATE DATABASE bpsimple WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;-- Table: item -- DROP TABLE item; CREATE TABLE item ( item_id serial NOT NULL,description character varying(64) NOT NULL,cost_price numeric(7,2),sell_price numeric(7,CONSTRAINT item_pk PRIMARY KEY (item_id ) ) WITH ( OIDS=FALSE ); ALTER TABLE item OWNER TO neil;
以上我直接从pgadmin 3 上的sql pane copy 下来的，是我模拟器上的现有数据，所以以上语句没有经过测试 !http://www.postgresql.org/docs/9.1/interactive/index.html 有问题的话,可以手册一下!
接下来我们还要对postgresql 进行一些配置已经进行外部的访问 ...
先进行访问授权 ...
#vim $PG_ROOT/data/pg_hda.conf
host bpsimple neil all trust
#vim postgresql.conf
listen_addresses = '*'
port = 5432
设置完监听端口后我们重启一下postgresql ...
su $PGUSER -c "$PGCTL stop -D '$PGDATA' -m fast"
su $PGUSER -c "$PGDAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
具体环境变量视不同机子而定，好吧，主题开始，首先编写一个pg类 ...
<?PHP class dbconn { private $linkid; // Postgresql link identifier private $host; // Postgresql server host private $db; // Postgresql database private $user; // Postgresql user private $passwd; // Postgresql password private $result; // Query result private $querycount; //Total queries excuted /* Class constructor. Initializes the $host,$user,$passwd and $db fields. */ function __construct($host,$db,$passwd) { $this->host = $host; $this->user = $user; $this->passwd = $passwd; $this->db = $db; } /* Connects to the Postgresql Database */ function connect() { try { $this->linkid = @pg_connect("host=$this->host dbname=$this->db user=$this->user password=$this->passwd"); if (!$this->linkid) throw new Exception("Could not connect to Postgresql server."); } catch (Exception $e) { die($e->getMessage()); } } /* Execute database query. */ function query($query) { try { $this->result = @pg_query($this->linkid,$query); if (!$this->result) throw new Exception("The database query Failed."); } catch (Exception $e) { echo $e->getMessage(); } $this->querycount++; return $this->result; } /* Determine total rows affected by query. */ function affectedRows() { $count = @pg_affected_rows($this->linkid); return $count; } /* Determine total rows returned by query */ function numRows() { $count = @pg_num_rows($this->result); return $count; } /* Return query result row as an object. */ function fetchObject() { $row = @pg_fetch_object($this->result); return $row; } /* Return query result row as an indexed array. */ function fetchRow() { $row = @pg_fetch_row($this->result); return $row; } /* Return query result row as an associated array. */ function fetchArray() { $row = @pg_fetch_array($this->result); return $row; } /* Return total number of queries executed during lifetime of this object. Not required,but interesting nonetheless. */ function numQueries() { return $this->querycount; } } ?>
然后开始进行调用吧 ...#vim dbtest.PHP
<html> <title> pgtest</title> <?PHP require_once 'dbconn.PHP'; $db = new dbconn("localhost","bpsimple","postgres",""); $db->connect(); $db->query('SELECT * FROM item'); echo 'number of row:' . $db->numRows(); ?> </html>
接下来就可以访问 http://hostname/pgPHP/dbtest.PHP
它会输入 item 的行数 ...
QQ:213572677 && linux c ph sql
Reference :
Beginning.PHP.and.Postgresql.8.From.Novice.to.Professional.Feb.2006
Beginning.Databases.With.Postgresql-From.Novice.To.Professional.2nd.Edition
Postgresql 9.1.3 docshttp://www.postgresql.org/docs/9.1/interactive/index.html
2012/4/10补充的sql初始化：
#su postgres pg$ /tmp/lnpp/pgsql/bin/createuser neil $ /tmp/lnpp/pgsql/bin/createdb bpsimple $ /tmp/lnpp/pgsql/bin/psql -U neil -d bpsimple create table item ( item_id serial,description varchar(64) not null,cost_price numeric(7,sell_price numeric(7,CONSTRAINT item_pk PRIMARY KEY(item_id) ); INSERT INTO item(description,cost_price,sell_price) VALUES('Wood Puzzle',15.23,21.95); INSERT INTO item(description,sell_price) VALUES('Rubik Cube',7.45,11.49); INSERT INTO item(description,sell_price) VALUES('Linux CD',1.99,2.49); INSERT INTO item(description,sell_price) VALUES('Tissues',2.11,3.99); INSERT INTO item(description,sell_price) VALUES('Picture Frame',7.54,9.95); INSERT INTO item(description,sell_price) VALUES('Fan Small',9.23,15.75); INSERT INTO item(description,sell_price) VALUES('Fan Large',13.36,19.95); INSERT INTO item(description,sell_price) VALUES('Toothbrush',0.75,1.45); INSERT INTO item(description,sell_price) VALUES('Roman Coin',2.34,2.45); INSERT INTO item(description,sell_price) VALUES('Carrier Bag',0.01,0.0); INSERT INTO item(description,sell_price) VALUES('Speakers',19.73,25.32);
2012/4/11 补充
有关有无密码登录,主要是在pg_hda.conf里面进行修改
host bpsimple neil all trust (无密码登录）
host bpsimple neil all md5 ( 需要密码）local bpsimple neil all trust (无密码登录） 设置用户密码: #su postgres $/tmp/lnpp/pgsql/bin/psql -d dbname -U postgres -c "alter role postgres password ‘yourpassword’;"
