restfull api神器 postgrest简单使用

参考github：https://github.com/PostgREST/postgrest

使用参考：http://postgrest.org/en/v7.0.0/api.html#

安装和部署二进制版本

前台运行：
./postgrest db.conf

后台运行：
nohup ./postgrest db.conf &

ps -ef | grep postgrest
kill -9 pid

endpoint

myip:3000/
myip:3000/yw_110jq
myip:3000/yw_110jq?limit=9
http://myip:3000/yw_110jq?limit=90&offset=30
http://myip:3000/yw_110jq?limit=90&offset=30&select=jqtime


# 模糊搜索，用*代替%
http://myip:3000/d_mlp?mpwzhm=like.%E6%99%AF%E5%AE%89%E5%8C%97%E8%A1%97*
http://myip:3000/d_mlp?mpwzhm=ilike.*%E6%99%AF%E5%AE%89%E5%8C%97%E8%A1%97*


# 全文搜索  有tsvector 或者tsquery字段才更好用。
http://myip:3000/xzqh_xz_pg?mc=plfts.%E6%BB%A8%E6%B1%9F%E8%A1%97%E9%81%93
http://myip:3000/xzqh_xz_pg?mc=phfts.%E6%BB%A8%E6%B1%9F%E8%A1%97%E9%81%93
http://myip:3000/xzqh_xz_pg?mc=wfts.%E5%A4%A7%E4%B8%9C%E8%A1%97%E9%81%93

select '*人民警察'::tsvector;
select '*人民警察'::tsquery;
select to_tsvector(mpwzhm) from d_mlp limit 20;


# 字段过滤
http://myip:3000/yw_110jq?limit=90&offset=30&select=jqtime,jqnr

# 字段重命名
http://myip:3000/yw_110jq?limit=90&offset=30&select=mytime:jqtime,myjqnr:jqnr

# 排序
http://myip:3000/yw_110jq?limit=90&offset=30&select=mytime:jqtime,myjqnr:jqnr&order=mytime.desc



# 更加复杂的查询，需要专门建立一个视图或者存储过程。

 

db.conf 配置文件内容如下：

db-uri = "postgres://username:[email protected]:port/dbname"
db-schema = "topology" # this schema gets added to the search_path of every request
db-anon-role = "postgres"
db-pool = 10
db-pool-timeout = 10

server-host = "!4"
server-port = 3000


## unix socket location
## if specified it takes precedence over server-port
# server-unix-socket = "/tmp/pgrst.sock"
## unix socket file mode
## when none is provided, 660 is applied by default
# server-unix-socket-mode = "660"

## base url for swagger output
# openapi-server-proxy-uri = ""

## choose a secret, JSON Web Key (or set) to enable JWT auth
## (use "@filename" to load from separate file)
# jwt-secret = "secret_with_at_least_32_characters"
# secret-is-base64 = false
# jwt-aud = "your_audience_claim"

## limit rows in response
# max-rows = 1000

## stored proc to exec immediately after auth
# pre-request = "stored_proc_name"

## jspath to the role claim key
# role-claim-key = ".role"

## extra schemas to add to the search_path of every request
# db-extra-search-path = "extensions, util"

## stored proc that overrides the root "/" spec
## it must be inside the db-schema
# root-spec = "stored_proc_name"

## content types to produce raw output
# raw-media-types="image/png, image/jpg"