下载postgresql,可以根据操作系统类型选择二进制包,解压后直接可以使用
https://github.com/begriffs/postgrest
其次,在postgresql中创建账户,并赋予select权限
stock=# create role tester login password '111111';
stock=# grant select on stock_info to tester;
创建配置文件psql.conf
db-uri = "postgres://tester:111111@localhost:5432/stock"
db-schema = "public"
db-anon-role = "tester"
db-pool = 10
server-host = "*4"
server-port = 3000
## base url for swagger output
# server-proxy-uri = ""
## choose a secret to enable JWT auth
## (use "@filename" to load from separate file)
# jwt-secret = "foo"
# secret-is-base64 = false
## limit rows in response
# max-rows = 1000
## stored proc to exec immediately after auth
# pre-request = "stored_proc_name"
执行nohup ./postgrest stock.conf > postgrest.log &
postgret的select查询规则如下所示,使用get方式提交请求:
http://localhost:3000/stock_info?select=close,tradingdate&tradingdate=lt.20160429&order=close.desc&limit=15
url中包含表名?select=字段名1,字段名2&过滤条件字段=比较运算符.值&order=排序字段.正序&limit=数量
其中比较运算符包括:
abbreviation | meaning |
---|---|
eq | equals |
gte | greater than or equal |
gt | greater than |
lte | less than or equal |
lt | less than |
neq | not equal |
like | LIKE operator (use * in place of %) |
ilike | ILIKE operator (use * in place of %) |
in | one of a list of values e.g. ?a=in.1,2,3 |
is | checking for exact equality (null,true,false) |
@@ | full-text search using to_tsquery |
@> | contains e.g. ?tags=@>.{example, new} |
<@ | contained in e.g. ?values=<@{1,2,3} |
not | negates another operator, see below |
由于postgrest不能执行复杂的sql查询命令,所以可以使用存储过程,将复杂的sql查询包成函数,提供给postgrest调用,例子如下:
首先在postgresql中定义存储过程:
CREATE FUNCTION add_them(a integer, b integer)
RETURNS integer AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL IMMUTABLE STRICT;
web端使用post方式,url中包含存储过程的名字,提交参数
POST /rpc/add_them HTTP/1.1
{ "a": 1, "b": 2 }
如果参数中有数组可以如下方式:
POST /rpc/native_array_func HTTP/1.1 { "arg": "{1,2,3}" }
参考
https://postgrest.com/en/v0.4/api.html#custom-queries