19.2. 存取PostgreSQL table

19.2.1. 生成资料

• URL：c

• POST资料：{"table":"table name","company":"company code","query":[column name,column value]}

• returns:["回传值"]

举例：

• 请求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[["f0","1"],["f4",0]]}' https://34.terarows.com/fastcgi/api/c

  可以把这道请求想像成要在dev5数据库上面执行这道SQL指令： INSERT INTO t52 (f0,f4) VALUES ('1',0)

• 回传：

  [["f2","2017-08-27 05:14:53.206975+00"],["f1",4]]

  因为f1与f2字段有设定预设值，所以在insert纪录后，回传其值。

19.2.2. 读取资料

• URL：r

• POST资料：{"and":boolean,"table":"table name","company":"company code","query":[[select column list],[query condition]]}

• returns:["回传纪录"]

举例：

• 请求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"and":true,"table":"t53","company":"1","query":[["f1","f2","f4","f6"],[["f1","=",1,null],["f4","BETWEEN",100,99999]]]}' https://34.terarows.com/fastcgi/api/r

  可以把这道请求想像成要在dev5数据库上面执行这道SQL指令： SELECT f1,f2,f4,f6 FROM t52 WHERE f1=1 AND f4 BETWEEN 100 AND 99999

• 回传：

  [[1,25,"3353","5127163"],[1,24,"3111","218811993"],[1,100,"3353","1"],[1,19,"2141","1724"],[1,18,"2141","1633"],[1,21,"2171","800"]]

19.2.3. 更改资料

• URL：u

• POST资料：{"table":"table name","company":"company code","query":[[key name,key value],[column name, new column value]]}

举例：

• 请求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[[["f0","1"],["f1",4]],[["f4",880],["f5",0]]]}' https://34.terarows.com/fastcgi/api/u

  可以把这道请求想像成要在dev5数据库上面执行这道SQL指令： UPDATE t52 SET f4=880,f5=0 WHERE f0='1' AND f1=4

19.2.4. 删除资料

• URL：d

• POST资料：{"table":"table name","company":"company code","query":[[key name,key value]]}

举例：

• 请求：

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[["f0","1"],["f1",4]]}' https://34.terarows.com/fastcgi/api/d

  可以把这道请求想像成要在dev5数据库上面执行这道SQL指令： DELETE FROM t52 WHERE f0='1' AND f1=4

19.2.5. 预先过滤function

如果在数据库有PostgreSQLapi_pre_check(IN TEXT,IN TEXT,IN TEXT,IN "char",IN TEXT)的function，则服务器於处理每一次API呼叫之前都会先执行这个function。如果这个function回传false或raise execption的话，则服务器忽略该次API请求，并且回传HTTP回应码401。（因为PostgreSQL通常被设定成记录exception的log而占据硬盘空间，所以请尽量避免制造exception。）

Function api_pre_check()的参数说明如下：

1. user id

2. table name

3. company code

4. c、r、u、或d

5. 条件

   以下面的API请求为例，

   curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"table":"t52","company":"1","query":[[["f0","1"],["f1",1]],[["f4",880],["f5",0]]]}' https://34.terarows.com/fastcgi/api/u

   条件内容就是"query" key的值：[[["f0","1"],["f1",1]],[["f4",880],["f5",0]]]

服务器於执行API之前转交这些参数给api_pre_check()。

设置api_pre_check()举例：

CREATE OR REPLACE FUNCTION api_pre_check(user_id TEXT,table_name TEXT,company TEXT,crud "char",query TEXT) RETURNS BOOLEAN AS $$
DECLARE
	result BOOLEAN;
	j JSONB;
BEGIN
	IF crud IN ('u','d') THEN 
		j:=query::JSONB;
		EXECUTE FORMAT('
			SELECT EXISTS (SELECT 1 FROM t43 WHERE f1=$1 AND f8)
				AND EXISTS (SELECT 1 FROM %I WHERE %I=%L AND %I=$2 AND %I=$3 AND (%I=$4 OR %I IS NULL))'
			,table_name
			,j #>> '{0,0,0}'
			,j #>> '{0,0,1}'
			,j #>> '{0,1,0}'
			,j #>> '{1,0,0}'
			,j #>> '{1,1,0}'
			,j #>> '{1,1,0}'
		)
		INTO result
		USING 
			user_id
			,(j #>> '{0,1,1}')::INTEGER
			,(j #>> '{1,0,1}')::INTEGER
			,(j #>> '{1,1,1}')::INTEGER;
		RETURN company='1' AND result;
	END IF;
	RETURN TRUE;
END $$ LANGUAGE PLPGSQL STABLE;