devbox
凡人多烦事01
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

使用PostgREST的RestAPI操作PostgreSQL数据库教程

作者：凡人多烦事01 | 2024-06-01 09:53:30

赞

踩

使用PostgREST的RestAPI操作PostgreSQL数据库教程

使用PostgREST的RestAPI操作PostgreSQL数据库教程

 

表和视图

公开的架构中的所有视图和表均可被查询的活动数据库角色访问，并且可供活动数据库角色访问。它们暴露在一级深度路线中。例如，表格人员的全部内容都在返回

GET /people HTTP/1.1

没有深层/嵌套/路线。每个路由都完全根据数据库权限提供OPTIONS，GET，POST，PATCH和DELETE动词。

注意

为什么不提供嵌套路线？许多API允许嵌套检索相关信息，例如/films/1/director。我们提供了一种更灵活的机制（受GraphQL启发）来嵌入相关信息。它可以处理一对多和多对多关系。关于资源嵌入的部分对此进行了介绍。

水平过滤（行）

您可以通过在列上添加条件来过滤结果行，每个条件都有一个查询字符串参数。例如，要返回13岁以下的人：

GET /people?age=lt.13 HTTP/1.1

多个参数可以通过以下方式逻辑组合：

GET /people?age=gte.18&student=is.true HTTP/1.1

多个参数可以通过以下方式在逻辑上脱节：

GET /people?or=(age.gte.14,age.lte.18) HTTP/1.1

也可以应用复杂的逻辑：

GET /people?and=(grade.gte.90,student.is.true,or(age.gte.14,age.is.null)) HTTP/1.1

运算符

这些运算符可用：

 

AbbreviationIn PostgreSQLMeaning
eq=equals
gt>greater than
gte>=greater than or equal
lt<less than
lte<=less than or equal
neq<> or !=not equal
likeLIKELIKE operator (use * in place of %)
ilikeILIKEILIKE operator (use * in place of %)
inINone 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")
isISchecking for exact equality (null,true,false)
fts@@Full-Text Search using to_tsquery
plfts@@Full-Text Search using plainto_tsquery
phfts@@Full-Text Search using phraseto_tsquery
wfts@@Full-Text Search using websearch_to_tsquery
cs@>contains e.g. ?tags=cs.{example, new}
cd<@contained in e.g. ?values=cd.{1,2,3}
ov&&overlap (have points in common), e.g. ?period=ov.[2017-01-01,2017-06-30] – also supports array types, use curly braces instead of square brackets e.g. :code: ?arr=ov.{1,3}
sl<<strictly left of, e.g. ?range=sl.(1,10)
sr>>strictly right of
nxr&<does not extend to the right of, e.g. ?range=nxr.(1,10)
nxl&>does not extend to the left of
adj-|-is adjacent to, e.g. ?range=adj.(1,10)
notNOTnegates another operator, see below
要否定任何运算符，请在其前面加上 not like  ?a=not.eq.2 或 ?not.and=(a.gte.0,a.lte.100) 。

对于更复杂的过滤器，您将必须在数据库中创建一个新视图，或使用存储过程。例如，下面是一个显示“今天的故事”的视图，其中可能包括较旧的固定故事：

  1. CREATE VIEW fresh_stories AS
  2. SELECT *
  3.   FROM stories
  4.  WHERE pinned = true
  5.     OR published > now() - interval '1 day'
  6. ORDER BY pinned DESC, published DESC;

该视图将提供一个新的端点：

GET /fresh_stories HTTP/1.1

重要

使用视图所有者的特权来调用视图，这与带有该选项的存储过程非常相似。由SUPERUSER角色创建时，除非指定了不同的非SUPERUSER所有者，否则所有行级安全性都将被绕过。SECURITY DEFINER

  1. -- Workaround:
  2. -- non-SUPERUSER role to be used as the owner of the views
  3. CREATE ROLE api_views_owner;
  4. -- alter the view owner so RLS can work normally
  5. ALTER VIEW sample_view OWNER TO api_views_owner;

全文搜索

fts上面提到的过滤器有很多选项来支持灵活的文本查询，即选择普通搜索还是词组搜索以及用于词干的语言。假设这tsearch是一个列my_tsv类型为tsvector的表。以下示例说明了可能性。

GET /tsearch?my_tsv=fts(french).amusant HTTP/1.1

GET /tsearch?my_tsv=plfts.The%20Fat%20Cats HTTP/1.1

GET /tsearch?my_tsv=not.phfts(english).The%20Fat%20Cats HTTP/1.1

GET /tsearch?my_tsv=not.wfts(french).amusant HTTP/1.1

使用词组搜索模式要求PostgreSQL的版本至少为9.6，并且将在数据库的早期版本中引发错误。

使用websearch_to_tsquery需要PostgreSQL的版本至少为11.0，并且会在数据库的早期版本中引发错误。

垂直过滤（列）

当某些列很宽时（例如那些保存二进制数据的列），服务器在响应中保留它们会更有效。客户端可以使用select参数指定需要哪些列。

  1. GET /people?select=first_name,age HTTP/1.1
  2.  
  3. [
  4.   {"first_name": "John", "age": 30},
  5.   {"first_name": "Jane", "age": 20}
  6. ]

默认值为*，表示所有列。在“ 资源嵌入”中，此值将变得更加重要。

重命名列

您可以通过在列之前添加别名和冒号:运算符来对列进行重命名。

  1. GET /people?select=fullName:full_name,birthDate:birth_date HTTP/1.1
  2.  
  3. [
  4.   {"fullName": "John Doe", "birthDate": "04/25/1988"},
  5.   {"fullName": "Jane Doe", "birthDate": "01/12/1998"}
  6. ]

铸造列

通过在双冒号::加所需类型的后缀后缀，可以铸造柱。

  1. GET /people?select=full_name,salary::text HTTP/1.1
  2.  
  3. [
  4.   {"full_name": "John Doe", "salary": "90000.00"},
  5.   {"full_name": "Jane Doe", "salary": "120000.00"}
  6. ]

JSON列

您可以根据PostgreSQL docs使用箭头运算符（或）指定json或jsonb列的路径。->->>

  1. GET /people?select=id,json_data->>blood_type,json_data->phones HTTP/1.1
  2.  
  3. [
  4.   { "id": 1, "blood_type": "A+", "phones": [{"country_code": "61", "number": "917-929-5745"}] },
  5.   { "id": 2, "blood_type": "O+", "phones": [{"country_code": "43", "number": "512-446-4988"}, {"country_code": "43", "number": "213-891-5979"}] }
  6. ]
  1. GET /people?select=id,json_data->phones->0->>number HTTP/1.1
  2.  
  3. [
  4.   { "id": 1, "number": "917-929-5745"},
  5.   { "id": 2, "number": "512-446-4988"}
  6. ]

计算列

过滤器可以应用于计算列（也称为虚拟列）以及实际的表/视图列，即使计算列不会出现在输出中。例如，要一次搜索名字和姓氏，我们可以创建一个计算列，该列将不会出现在输出中，但可以在过滤器中使用：

  1. CREATE TABLE people (
  2.   fname text,
  3.   lname text
  4. );
  5.  
  6. CREATE FUNCTION full_name(people) RETURNS text AS $$
  7.   SELECT $1.fname || ' ' || $1.lname;
  8. $$ LANGUAGE SQL;
  9.  
  10. -- (optional) add an index to speed up anticipated query
  11. CREATE INDEX people_full_name_idx ON people
  12.   USING GIN (to_tsvector('english', full_name(people)));

在计算列上的全文本搜索：

GET /people?full_name=fts.Beckett HTTP/1.1

如前所述，默认情况下，计算列不会出现在输出中。但是，您可以通过在垂直过滤select参数中列出它们来包括它们：

GET /people?select=*,full_name HTTP/1.1

重要

必须在公开的架构下创建计算列才能以这种方式使用。

Unicode支持

PostgREST支持模式，表，列和值中的unicode。要访问具有Unicode名称的表，请使用百分比编码。

要求此：

GET /موارد HTTP/1.1

做这个：

GET /%D9%85%D9%88%D8%A7%D8%B1%D8%AF HTTP/1.1

带有空格的表/列

您可以通过以下方式对表/列中的空格进行编码%20：

GET /Order%20Items?Unit%20Price=lt.200 HTTP/1.1

保留字符

如果过滤器包括PostgREST保留字符（,，.，:，()）你必须围绕这些百分比编码双引号%22进行正确的处理。

这里Hebdon,John和Williams,Mary是值。

GET /employees?name=in.(%22Hebdon,John%22,%22Williams,Mary%22) HTTP/1.1

这information.cpe是列名。

GET /vulnerabilities?%22information.cpe%22=like.*MS* HTTP/1.1

注意

一些http库可能会自动编码URL（例如axios）。在这种情况下，您应该""直接使用双引号 而不是%22。

订购

保留字order对响应行进行重新排序。它使用逗号分隔的列和方向列表：

GET /people?order=age.desc,height.asc HTTP/1.1

如果未指定方向，则默认为升序：

GET /people?order=age HTTP/1.1

如果您关心null的排序位置，请先添加nullsfirst或nullslast：

GET /people?order=age.nullsfirst HTTP/1.1

GET /people?order=age.desc.nullslast HTTP/1.1

您也可以使用“ 计算列”对结果进行排序，即使计算列不会出现在输出中。

限制和分页

PostgREST使用HTTP范围标头来描述结果的大小。每个响应均包含当前范围以及（如果要求）结果总数：

  1. HTTP/1.1 200 OK
  2. Range-Unit: items
  3. Content-Range: 0-14/*

这里返回零到十四的项目。该信息在每个响应中都可用，并且可以帮助您在客户端上呈现分页控件。这是一个符合RFC7233的解决方案，可以使响应JSON更干净。

应用限制行和偏移行的方式有两种：通过请求标头或查询参数。使用标题时，请指定所需的行范围。该请求将获得前20个人。

  1. GET /people HTTP/1.1
  2. Range-Unit: items
  3. Range: 0-19

请注意，如果无法满足您的请求，服务器的响应可能会更少：

  1. HTTP/1.1 200 OK
  2. Range-Unit: items
  3. Content-Range: 0-17/*

您也可以要求无限制范围的开放范围，例如。Range: 10-

请求限制或偏移量的另一种方法是使用查询参数。例如

GET /people?limit=15&offset=30 HTTP/1.1

此方法对于嵌入式资源也很有用，我们将在另一部分中介绍。即使您使用查询参数来限制查询，服务器也会始终以范围标头作为响应。

为了获得表或视图的总大小（例如在分页控件中呈现最后一个页面链接时），请在请求标头中指定您的首选项：

  1. GET /bigtable HTTP/1.1
  2. Range-Unit: items
  3. Range: 0-24
  4. Prefer: count=exact

请注意，表越大，查询在数据库中运行的速度就越慢。服务器将响应所选范围和总计

  1. HTTP/1.1 206 Partial Content
  2. Range-Unit: items
  3. Content-Range: 0-24/3573458

响应格式

PostgREST使用适当的HTTP内容协商（RFC7231）来传递所需的资源表示形式。也就是说，相同的API终结点可以根据客户端请求以JSON或CSV等不同格式进行响应。

使用接受请求标头为响应指定可接受的格式：

  1. GET /people HTTP/1.1
  2. Accept: application/json

当前的可能性是

  • * / *
  • 文字/ csv
  • 应用程序/ json
  • 应用程序/ openapi + json
  • 应用/八位字节流

对于API端点和根目录上的OpenAPI，服务器将默认为JSON。

单数或复数

默认情况下，即使只有一项，PostgREST也会以数组形式返回所有JSON结果。例如，要求/items?id=eq.1退货

  1. [
  2.   { "id": 1 }
  3. ]

这对于客户端代码可能很不方便。要将第一个结果作为数组未包含的对象返回，请指定vnd.pgrst.object为Accept标头的一部分

  1. GET /items?id=eq.1 HTTP/1.1
  2. Accept: application/vnd.pgrst.object+json

这返回

{ "id": 1 }

当请求单个响应但未找到任何条目时，服务器将以错误消息和406 Not Acceptable状态码响应，而不是通常的空数组和200状态：

  1. {
  2.   "message": "JSON object requested, multiple (or no) rows returned",
  3.   "details": "Results contain 0 rows, application/vnd.pgrst.object+json requires 1 row"
  4. }

注意

许多API使用特殊的嵌套URL约定（例如/ stories vs / stories / 1）来区分多个资源和单个资源。为什么我们使用/stories?id=eq.1？答案是因为单数资源（对我们而言）是由主键确定的行，并且主键可以是复合键（意思是在多个列上定义）。较为熟悉的嵌套url仅考虑简单且压倒性的数字主键的简并情况。这些所谓的人工密钥通常由对象关系映射库自动引入。

诚然，PostgREST可以检测到构成主键的所有列上是否存在相等条件，并自动将其转换为单数。但是，这可能会导致格式发生令人惊讶的变化，仅通过过滤额外的列就可以打破不必要的客户端代码。相反，我们允许手动指定单数或复数，以使该选择与URL格式脱钩。

资源嵌入

除了为每个表和视图提供RESTful路由外，PostgREST还允许将相关资源一起包含在单个API调用中。这减少了对多个API请求的需求。服务器使用外键确定可以一起返回哪些表和视图。例如，考虑电影及其奖项的数据库：

重要

PostgREST需要FOREIGN KEY约束才能进行资源嵌入。

_images / film.png

如上文“ 垂直过滤（列）”所示，我们可以要求所有电影的标题，如下所示：

GET /films?select=title HTTP/1.1

这可能会返回类似

  1. [
  2.   { "title": "Workers Leaving The Lumière Factory In Lyon" },
  3.   { "title": "The Dickson Experimental Sound Film" },
  4.   { "title": "The Haunted Castle" }
  5. ]

但是，由于电影和导演之间存在外键约束，因此我们可以要求包含以下信息：

GET /films?select=title,directors(id,last_name) HTTP/1.1

哪个会回来

  1. [
  2.   { "title": "Workers Leaving The Lumière Factory In Lyon",
  3.     "directors": {
  4.       "id": 2,
  5.       "last_name": "Lumière"
  6.     }
  7.   },
  8.   { "title": "The Dickson Experimental Sound Film",
  9.     "directors": {
  10.       "id": 1,
  11.       "last_name": "Dickson"
  12.     }
  13.   },
  14.   { "title": "The Haunted Castle",
  15.     "directors": {
  16.       "id": 3,
  17.       "last_name": "Méliès"
  18.     }
  19.   }
  20. ]

在此示例中，由于该关系是前向关系，因此只有一位导演与电影关联。由于表名是复数形式，因此最好改为单数形式。表名别名可以实现以下目的：

GET /films?select=title,director:directors(id,last_name) HTTP/1.1

重要

每当数据库模式中的FOREIGN KEY约束发生更改时，都必须刷新PostgREST的模式缓存，以使“资源嵌入”正常工作。请参阅“ 模式重新加载 ”部分。

通过联接表嵌入

PostgREST还可以检测通过联接表进行的关系。因此，您可以请求电影演员（在这种情况下，通过角色来查找信息）。您还可以反转包容的方向，要求所有导演，包括其电影列表：

GET /directors?select=films(title,year) HTTP/1.1

嵌入式过滤器

嵌入式资源的形状可以与其顶级资源相似。为此，请在查询参数前面加上嵌入式资源的名称。例如，要订购每部电影中的演员：

GET /films?select=*,actors(*)&actors.order=last_name,first_name HTTP/1.1

这个排序在每一部电影的演员名单，但并没有改变电影本身的顺序。要过滤每部电影返回的角色：

GET /films?select=*,roles(*)&roles.character=in.(Chico,Harpo,Groucho) HTTP/1.1

再一次，这将角色包括在某些角色中，但不以任何方式过滤电影。没有这些字符的电影将与空字符列表一起包括在内。

一个or过滤器可以用于类似的操作：

GET /films?select=*,roles(*)&roles.or=(character.eq.Gummo,character.eq.Zeppo) HTTP/1.1

限制和偏移操作是可能的：

GET /films?select=*,actors(*)&actors.limit=10&actors.offset=2 HTTP/1.1

可以为嵌入式资源添加别名，并可以对这些别名应用过滤器：

GET /films?select=*,90_comps:competitions(name),91_comps:competitions(name)&90_comps.year=eq.1990&91_comps.year=eq.1991 HTTP/1.1

嵌入浏览

如果视图包含在源表中定义了外键的列，则可以嵌入视图。

作为示例，让我们nominations_view基于提名表创建一个名为的视图。

  1. CREATE VIEW nominations_view AS
  2. SELECT
  3.    rank
  4.  , competition_id
  5.  , film_id
  6. FROM
  7.   nominations;

由于其中包含competition_id和film_id，并且每个人的源表中都有一个外键，因此我们可以嵌入竞赛和电影：

GET /nominations_view?select=rank,competitions(name,year),films(title)&rank=eq.5 HTTP/1.1

也可以嵌入物化视图。

警告

不能保证所有视图都是可嵌入的。特别是，包含UNION的视图将不可嵌入。

为什么？PostgREST通过查询和解析pg_rewrite在视图中检测源表外键。根据视图的复杂性，这可能会失败。

如果您的视图无法嵌入，请报告问题，以便我们继续改进外键检测。

将来，我们将提供一种方法来手动指定视图源外键以解决此限制。

重要

如果视图定义发生更改，则必须刷新PostgREST的架构缓存才能使其正常工作。请参阅“ 模式重新加载 ”部分。

自定义查询

PostgREST URL语法限制了客户端可以执行的查询类型。它可以防止任意的，可能构造不良的和缓慢的客户端查询。这对服务质量有好处，但是意味着数据库管理员必须创建自定义视图和存储过程以提供更丰富的端点。自定义端点的最常见原因是

  • 表联合
  • 比资源嵌入提供的连接更复杂
  • 需要参数的地理空间查询，例如“（纬度，经度）附近的点”
  • 比简单使用fts过滤器更复杂的全文本搜索

存储过程

可以在/rpc前缀下访问API公开的数据库模式中的每个存储过程。API端点支持POST（在某些情况下为GET）来执行该功能。

POST /rpc/function_name HTTP/1.1

这些函数可以执行PostgreSQL允许的任何操作（读取数据，修改数据，甚至进行DDL操作）。

要在API调用中提供参数，请在请求有效负载中包含一个JSON对象，并且该对象的每个键/值都将成为参数。

例如，假设我们已经在数据库中创建了此函数。

  1. CREATE FUNCTION add_them(a integer, b integer)
  2. RETURNS integer AS $$
  3.  SELECT a + b;
  4. $$ LANGUAGE SQL IMMUTABLE;

客户端可以通过发布一个对象来调用它

  1. POST /rpc/add_them HTTP/1.1
  2.  
  3. { "a": 1, "b": 2 }
  4.  
  5. 3

重要

每当您创建或更改函数时，都必须刷新PostgREST的架构缓存。请参阅“ 模式重新加载 ”部分。

如果未刷新架构缓存，则PostgREST将假定text为函数参数的默认类型。这可能会导致获得错误响应，例如：

  1. {
  2.  "hint":"No function matches the given name and argument types. You might need to add explicit type casts.",
  3.  "details":null,
  4.  "code":"42883",
  5.  "message":"function test.add_them(a => text, b => text) does not exist"
  6. }

您还可以通过发送带有请求的标头来调用采用json类型的单个参数的函数。这样，JSON请求主体将用作单个参数。Prefer: params=single-object

  1. CREATE FUNCTION mult_them(param json) RETURNS int AS $$
  2.   SELECT (param->>'x')::int * (param->>'y')::int
  3. $$ LANGUAGE SQL;
  1. POST /rpc/mult_them HTTP/1.1
  2. Prefer: params=single-object
  3.  
  4. { "x": 4, "y": 2 }
  5.  
  6. 8

必须使用命名参数声明过程，过程声明如下：

CREATE FUNCTION non_named_args(integer, text, integer) ...

无法在PostgREST中调用，因为我们在内部使用命名符号。

请注意，PostgreSQL会将标识符名称转换为小写，除非您用如下方式引用它们：

CREATE FUNCTION "someFunc"("someParam" text) ...

PostgreSQL具有四种程序语言，它们是核心发行版的一部分：PL / pgSQL，PL / Tcl，PL / Perl和PL / Python。还有许多其他程序语言作为附加扩展分发。同样，纯SQL可以用于编写函数（如上面的示例所示）。

注意

对于PostgreSQL 10之前的版本，要传递PostgreSQL本机数组，您需要将其引用为字符串：

  1. POST /rpc/native_array_func HTTP/1.1
  2.  
  3. { "arg": "{1,2,3}" }

在这些版本中，我们建议使用json类型的函数参数来接受来自客户端的数组：

  1. POST /rpc/json_array_func HTTP/1.1
  2.  
  3. { "arg": [1,2,3] }

从PostgreSQL 10开始，来自客户端的json数组通常会映射到PostgreSQL本机数组。

注意

为什么使用/ rpc前缀？原因之一是避免视图和过程之间的名称冲突。它还有助于向API使用者强调这些功能不是正常的烦人的事情。这些功能可能具有任意和令人惊讶的行为，而不是用户期望从其他途径获得的标准“后期创建资源”。

稳定的功能

PostgreSQL中标有stable或易失immutable 性的过程只能读取，不能修改数据库，而PostgREST在与只读副本兼容的只读事务中执行它们。如果需要，可以使用HTTP GET动词调用稳定和不可变的函数。

注意

波动性标志是有关功能行为的承诺。PostgreSQL将允许您将修改数据库的功能标记为immutable/stable无故障。但是，通过PostgREST调用时，该函数将失败，因为它是在只读事务中执行该函数。

因为add_them被声明为IMMUTABLE，所以我们可以用GET请求交替调用该函数：

GET /rpc/add_them?a=1&b=2 HTTP/1.1

函数参数名称在POST情况下与JSON对象键匹配，对于GET情况，它们与查询参数匹配?a=1&b=2。

标量函数

PostgREST将检测该函数是标量的还是表值的，并将相应地调整响应格式：

  1. GET /rpc/add_them?a=1&b=2 HTTP/1.1
  2.  
  3. 3
  1. GET /rpc/best_films_2017 HTTP/1.1
  2.  
  3. [
  4.   { "title": "Okja", "rating": 7.4},
  5.   { "title": "Call me by your name", "rating": 8},
  6.   { "title": "Blade Runner 2049", "rating": 8.1}
  7. ]

大话务量

类似于Bulk Insert，可以批量调用函数。

  1. POST /rpc/add_them HTTP/1.1
  2. Content-Type: application/json
  3.  
  4. [
  5.    {"a": 1, "b": 2},
  6.    {"a": 3, "b": 4}
  7. ]

结果：

[ 3, 7 ]

也可以在函数调用中指定列。

功能过滤器

返回表类型响应的函数可以使用与用于表和视图的过滤器相同的过滤器来整形：

CREATE FUNCTION best_films_2017() RETURNS SETOF films ..

GET /rpc/best_films_2017?select=title,director:directors(*) HTTP/1.1

GET /rpc/best_films_2017?rating=gt.8&order=title.desc HTTP/1.1

功能权限

默认情况下，函数以调用它的用户的特权执行。这意味着用户必须具有所有权限才能执行该过程执行的操作。

另一种选择是使用该选项定义功能。然后，将只进行一次权限检查，即调用该功能的权限，并且该功能中的操作将拥有拥有该功能本身的用户的权限。有关更多详细信息，请参见PostgreSQL文档。SECURITY DEFINER

警告

与表/视图不同，函数特权用作黑名单，因此默认情况下，它们对于所有角色都是可执行的。您可以通过撤销功能的PUBLIC特权，然后将特权授予特定角色来解决此问题：

  1. REVOKE ALL PRIVILEGES ON FUNCTION private_func() FROM PUBLIC;
  2. GRANT EXECUTE ON FUNCTION private_func() TO a_role;

另外，为了避免执行REVOKE所有功能，您可以默认使用以下方式启用此行为：

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

有关更多详细信息，请参见PostgreSQL alter default特权。

重载函数

您可以使用不同数量的参数调用重载函数。

  1. CREATE FUNCTION rental_duration(customer_id integer) ..
  2.  
  3. CREATE FUNCTION rental_duration(customer_id integer, from_date date) ..
GET /rpc/rental_duration?customer_id=232 HTTP/1.1

GET /rpc/rental_duration?customer_id=232&from_date=2018-07-01 HTTP/1.1

访问请求头，Cookie和JWT声明

通过读取每个请求的PostgREST设置的GUC变量，存储过程可以访问请求标头，cookie和jwt声明。它们被命名为request.header.XYZ，request.cookie.XYZ和request.jwt.claim.XYZ。

  1. -- To read the value of the Origin request header:
  2. SELECT current_setting('request.header.origin', true);
  3. -- To read the value of sessionId in a cookie:
  4. SELECT current_setting('request.cookie.sessionId', true);
  5. -- To read the value of the email claim in a jwt:
  6. SELECT current_setting('request.jwt.claim.email', true);

注意

request.jwt.claim.role缺省为db-anon-role的值。

设置响应头

PostgREST读取response.headersSQL变量以将额外的标头添加到HTTP响应。存储过程可以修改此变量。例如，此语句将在响应中添加缓存头：

  1. -- tell client to cache response for two days
  2.  
  3. SET LOCAL "response.headers" =
  4.   '[{"Cache-Control": "public"}, {"Cache-Control": "max-age=259200"}]';

请注意，该变量应设置为单键对象数组，而不是单个多键对象。这是因为设置多个值时，必须重复标头（例如Cache-Control或）Set-Cookie，并且对象不允许重复的键。

错误和HTTP状态代码

通过引发SQL异常，存储过程可以返回非200 HTTP状态代码。例如，这是一个伪函数，总是会返回错误：

  1. CREATE OR REPLACE FUNCTION just_fail() RETURNS void
  2.   LANGUAGE plpgsql
  3.   AS $$
  4. BEGIN
  5.   RAISE EXCEPTION 'I refuse!'
  6.     USING DETAIL = 'Pretty simple',
  7.           HINT = 'There is nothing you can do.';
  8. END
  9. $$;

调用该函数将返回带有正文的HTTP 400

  1. {
  2.   "message":"I refuse!",
  3.   "details":"Pretty simple",
  4.   "hint":"There is nothing you can do.",
  5.   "code":"P0001"
  6. }

定制HTTP状态代码的一种方法是根据PostgREST 错误到状态代码的映射引发特定异常。例如，将使用HTTP 401/403进行响应。RAISE insufficient_privilege

为了更好地控制HTTP状态代码，请引发PTxyz类型的异常。例如，使用HTTP 402进行响应，请举起'PT402'：

  1. RAISE sqlstate 'PT402' using
  2.   message = 'Payment Required',
  3.   detail = 'Quota exceeded',
  4.   hint = 'Upgrade your plan';

返回值：

  1. HTTP/1.1 402 Payment Required
  2. Content-Type: application/json; charset=utf-8
  3.  
  4. {"hint":"Upgrade your plan","details":"Quota exceeded"}

插入/更新

可以根据请求者的数据库角色的权限，通过API修改所有表和自动更新的视图。

要在数据库表中创建行，请发布一个JSON对象，该对象的键是要创建的列的名称。适用时，缺少的属性将设置为默认值。

  1. POST /table_name HTTP/1.1
  2.  
  3. { "col1": "value1", "col2": "value2" }

该响应将包含一个Location标题，该标题描述了在哪里可以找到新对象。如果表是只写的，那么构造Location标头将导致权限错误。要将项目成功插入到只写表中，您需要通过包含request标头来抑制Location响应标头。Prefer: return=minimal

在频谱的另一端，可以通过包含标头将完整的创建对象返回给您的请求响应。这样，您就不必进行其他HTTP调用来发现可能已在服务器端填充的属性。您也可以将标准垂直过滤（列）应用于这些结果。Prefer: return=representation

URL编码的有效载荷可以使用来发布。Content-Type: application/x-www-form-urlencoded

  1. POST /people HTTP/1.1
  2. Content-Type: application/x-www-form-urlencoded
  3.  
  4. name=John+Doe&age=50&weight=80

注意

插入行时，您必须发布一个JSON对象，而不是用引号引起来的JSON。

  1. Yes
  2. { "a": 1, "b": 2 }
  3.  
  4. No
  5. "{ \"a\": 1, \"b\": 2 }"

如果您不小心，某些javascript库会错误地发布数据。为了获得最佳结果，请尝试为PostgREST构建的客户端库之一。

要更新表中的一行或多行，请使用PATCH动词。使用水平过滤（行）来指定要更新的记录。这是一个示例查询，将category特定年龄以下的所有人的列设置为child。

  1. PATCH /people?age=lt.13 HTTP/1.1
  2.  
  3. { "category": "child" }

更新还支持加垂直滤波（列）。Prefer: return=representation

警告

当心意外更新表中的每一行。要了解如何防止这种情况，请参阅“ 阻止全表操作”。

警告

使用PostgREST可能无法在具有复杂规则的VIEW上插入。建议您使用触发器而不是RULEs。如果要继续使用RULE，一种解决方法是将VIEW插入包装在存储过程中，然后通过“ 存储过程”界面调用它。

批量插入

批量插入的工作原理与单行插入完全相同，不同之处在于，您可以提供具有统一键的对象的JSON数组或CSV格式的行。这不仅使所需的HTTP请求最小化，而且在后端使用单个INSERT语句以提高效率。请注意，使用CSV需要较少的服务器解析，并且速度更快。

要批量插入CSV，只需将发布到的表格路由中，并在第一行中包含列名即可。例如Content-Type: text/csv

  1. POST /people HTTP/1.1
  2. Content-Type: text/csv
  3.  
  4. name,age,height
  5. J Doe,62,70
  6. Jonas,10,55

空字段（,,）强制为空字符串，保留字NULL映射为SQL空值。请注意，列名和逗号之间不应有空格。

要批量插入JSON，请发布具有所有匹配键的对象数组

  1. POST /people HTTP/1.1
  2. Content-Type: application/json
  3.  
  4. [
  5.   { "name": "J Doe", "age": 62, "height": 70 },
  6.   { "name": "Janus", "age": 10, "height": 55 }
  7. ]

指定列

通过使用columns查询参数，可以指定将要插入/更新的有效负载密钥，而忽略其余的有效负载。

  1. POST /datasets?columns=source,publication_date,figure HTTP/1.1
  2. Content-Type: application/json
  3.  
  4. {
  5.   "source": "Natural Disaster Prevention and Control",
  6.   "publication_date": "2015-09-11",
  7.   "figure": 1100,
  8.   "location": "...",
  9.   "comment": "...",
  10.   "extra": "...",
  11.   "stuff": "..."
  12. }

在这种情况下，将仅插入source，publication_date和Figure。其余JSON键将被忽略。

使用此方法还具有提高批量插入效率的副作用，因为PostgREST不会处理JSON，而是将其直接发送到PostgreSQL。

UPSERT 

您可以使用POST和标头制作UPSERT ：Prefer: resolution=merge-duplicates

  1. POST /employees HTTP/1.1
  2. Prefer: resolution=merge-duplicates
  3.  
  4. [
  5.   { "id": 1, "name": "Old employee 1", "salary": 30000 },
  6.   { "id": 2, "name": "Old employee 2", "salary": 42000 },
  7.   { "id": 3, "name": "New employee 3", "salary": 50000 }
  8. ]

UPSERT基于主键列进行操作，您必须指定所有这些键。您还可以选择使用忽略重复项。当主键是自然键时，UPSERT效果最好，但是如果主键是替代键，则还可以使用它（例如：“ id串行主键”）。有关更多详细信息，请阅读此问题。Prefer: resolution=ignore-duplicates

重要

创建表或更改其主键后，必须刷新PostgREST模式缓存以使UPSERT正常工作。要了解如何刷新缓存，请参阅Schema Reloading。

可以通过以下方式使用PUT和过滤主键列来完成单行UPSERT eq：

  1. PUT /employees?id=eq.4 HTTP/1.1
  2.  
  3. { "id": 4, "name": "Sara B.", "salary": 60000 }

必须在请求正文中指定所有列，包括主键列。

注意

该功能仅从PostgreSQL 9.5开始可用，因为它使用了ON CONFLICT子句。

删除

要删除表中的行，请使用DELETE动词加上“ 水平过滤（行）”。例如删除不活动的用户：

DELETE /user?active=is.false HTTP/1.1

警告

当心意外删除表中的所有行。要了解如何防止这种情况，请参阅“ 阻止全表操作”。

二进制输出

如果要从一bytea列返回原始二进制数据，则必须指定application/octet-stream作为Accept标题的一部分，然后选择一个列?select=bin_data。

  1. GET /items?select=bin_data&id=eq.1 HTTP/1.1
  2. Accept: application/octet-stream

您还可以在调用存储过程时请求二进制输出，由于它们可以返回标量值，因此select 在这种情况下您不必强制使用。

CREATE FUNCTION closest_point(..) RETURNS bytea ..

  1. POST /rpc/closest_point HTTP/1.1
  2. Accept: application/octet-stream

如果存储过程返回非标量值，则需要执行select与GET二进制输出相同的方法。

CREATE FUNCTION overlapping_regions(..) RETURNS SETOF TABLE(geom_twkb bytea, ..) ..

  1. POST /rpc/overlapping_regions?select=geom_twkb HTTP/1.1
  2. Accept: application/octet-stream

注意

如果将返回多于一行，则二进制结果将被串联，并且没有定界符。

纯文本输出

您可以text使用来从列中获取原始输出。Accept: text/plain

  1. GET /workers?select=custom_psv_format HTTP/1.1
  2. Accept: text/plain
  3.  
  4. 09310817|JOHN|DOE|15/04/88|
  5. 42152780|FRED|BLOGGS|20/02/85|
  6. 43006541|OTTO|NORMALVERBRAUCHER|01/07/90|
  7. 02452492|ERIKA|MUSTERMANN|11/01/80|

这遵循与Binary Output相同的规则。

OpenAPI的支持

PostgREST托管的每个API 都会在根路径上自动提供完整的OpenAPI描述。这提供了所有端点（表，外部表，视图，函数）的列表，以及受支持的HTTP动词和示例有效负载。为了进行额外的自定义，OpenAPI输出包含任何数据库对象上每个SQL注释的“描述”字段。例如，

  1. COMMENT ON SCHEMA mammals IS
  2.   'A warm-blooded vertebrate animal of a class that is distinguished by the secretion of milk by females for the nourishment of the young';
  3.  
  4. COMMENT ON TABLE monotremes IS
  5.   'Freakish mammals lay the best eggs for breakfast';
  6.  
  7. COMMENT ON COLUMN monotremes.has_venomous_claw IS
  8.   'Sometimes breakfast is not worth it';

这些不良注释将在info.description，definitions.monotremes.description和中显示为生成的JSON definitions.monotremes.properties.has_venomous_claw.description。

同样，如果您希望生成一个summary字段，则可以通过多行注释来做到这一点，summary它将是第一行以及其后description的行：

  1. COMMENT ON TABLE entities IS
  2.   $$Entities summary
  3.  
  4.   Entities description that
  5.   spans
  6.   multiple lines$$;

您可以使用Swagger UI之类的工具根据说明创建漂亮的文档，并托管基于Web的交互式仪表板。该仪表板允许开发人员针对实时的PostgREST服务器发出请求，并提供有关请求标头和示例请求正文的指南。

重要

随着架构在运行的服务器下更改，OpenAPI信息可能会过时。要了解如何刷新缓存，请参阅Schema Reloading。

 

HTTP状态码

PostgREST将PostgreSQL错误代码转换为HTTP状态，如下所示：

 

PostgreSQL error code(s)HTTP statusError description
08*503pg connection err
09*500triggered action exception
0L*403invalid grantor
0P*403invalid role specification
23503409foreign key violation
23505409uniqueness violation
25*500invalid transaction state
28*403invalid auth specification
2D*500invalid transaction termination
38*500external routine exception
39*500external routine invocation
3B*500savepoint exception
40*500transaction rollback
53*503insufficient resources
54*413too complex
55*500obj not in prerequisite state
57*500operator intervention
58*500system error
F0*500conf file error
HV*500foreign data wrapper error
P0001400default code for “raise”
P0*500PL/pgSQL error
XX*500internal error
42883404undefined function
42P01404undefined table
42501

if authenticated 403,

else 401

insufficient privileges
other400 
 

翻译原文：http://postgrest.org/en/v6.0/api.html#operators

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/657175
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号