[TOC] ## 参数过滤 | 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`– also supports commas in quoted strings like`?a=in."hi,there","yes,you"` | | 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 | 如 ``` /people?age=gte.18&student=is.true /people?age=lt.13 ``` ### 过滤字段 直接过滤字段 ``` /people?select=fname,age ``` 使用函数,创建一个 full_name 函数 ``` CREATE TABLE people ( fname text, lname text ); CREATE FUNCTION full_name(people) RETURNS text AS $$ SELECT $1.fname || ' ' || $1.lname; $$ LANGUAGE SQL; -- (optional) add an index to speed up anticipated query CREATE INDEX people_full_name_idx ON people USING GIN (to_tsvector('english', full_name(people))); ``` curl ``` /people?full_name=@@.Beckett /people?select=*,full_name ``` ### 排序 ``` /people?order=age 默认是 asc /people?order=age.desc,height.asc /people?order=age.nullsfirst 存在 null 时,设置 null 优先还是最后 /people?order=age.desc.nullslast ``` ### Limit 和分页 ``` /people?limit=15&offset=30 ``` ### 返回 JSON 或 JSON 数组 默认返回 JSON 数组 ``` [ { "id": 1 } ] ``` 设置头 ``` Accept: application/vnd.pgrst.object+json ``` 即可返回单数 ``` { "id": 1 } ``` ### 批量插入 ``` POST /people HTTP/1.1 Content-Type: application/json [ { "name": "J Doe", "age": 62, "height": 70 }, { "name": "Janus", "age": 10, "height": 55 } ] ``` ### 删除 ``` DELETE /user?active=is.false HTTP/1.1 ```