[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 ```