postgrest 简单使用

下载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