Postgrest is third party REST API interface for quering postgres SQL server,which let us access data from postgres tables,views & function.Lets explore it further.
First we will download postgrest(https://github.com/begriffs/postgrest/releases/tag/v0.3.1.0). For my debian machine I am using ubuntu version of postgrest (postgrest-0.3.1.0-ubuntu.tar.xz),Extract it and rename it to just "postgrest",You can preferably move it to "/opt/".
We can run it as follows from Linux Terminal:
cd /opt
./postgrest 'postgres://mypgusername:mypassword@localhost:5432/mypgdb' -a mypgusername -p 2020
Note: Here first argument is jdbc url to our postgres server."mypgusername" is database role & 2020 is port on which Postgrest will run.
role can be "anonymous".
We need some tables to check Postgrest functionality.Lets create below table
create table t_review
(
review_id uuid,
user_id uuid,
product_id uuid,
review_headline varchar,
verbose_response text,
review_date timestamp without time zone,
status record_status
)
create table t_review_response
(
response_id uuid,
review_id uuid,
user_id uuid,
vote smallint,
vote_date timestamp without time zone,
status record_status
)
Add some dummy data in both tables please keep in mind "review_id" in "t_review_response" should belong to "t_review"
You may need to give access right to this table to user through which postgrest is running
GRANT SELECT ON TABLE t_review TO mypgusername;
GRANT SELECT,INSERT,UPDATE ON playground.public.t_review_response to mypgusername;
As some columns are declared of type uuid,to generate new uuid in postgres we might need to add corresponding extension if not already installed.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Now we can read data from any table using GET as follows
API End Point :http://localhost:2020/t_review_response
API Response:
[
{
"response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote": 1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
Where Clause:
a) Equality:
API End Point :http://localhost:2020/t_review_response?vote=eq.-1&review_id=eq.8e03e047-7eea-4725-8e5d-5c3dded7c0b9&response_id=eq.75320cf9-e5ca-4844-a2ae-ae6a44466fa9
API Response:
[
{
"response_id": "75320cf9-e5ca-4844-a2ae-ae6a44466fa9",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "c7de1c3d-0172-44f2-ad92-88bb9eed1918",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
b) In clause:
API End Point :http://localhost:2020/t_review_response?response_id=in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
API Response:
[
{
"response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote": 1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
c) like Clause:
API End Point :http://localhost:2020/t_review?review_headline=like.*battery*
API Response:
[
{
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "4ace0309-b355-403f-9777-f046c157a9b0",
"product_id": "be4b2c18-3afb-41ec-9a6b-157be393eb03",
"review_headline": "LG V20 battery backup",
"verbose_response": "Here is my review",
"review_date": "2017-06-28T14:53:46.974368",
"status": "active"
}
]
Postgrest uses * instead of % in like queries unlike sql queries.
d) not like clause:
API End Point : http://localhost:2020/t_review?review_headline=not.like.*battery*
API Response:
[
{
"review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
"user_id": "7b326e04-3f12-45a6-9ec2-1015491fccce",
"product_id": "50514530-e1a7-4904-b4f4-4244d3579658",
"review_headline": "LG V20 Speaker Sound Quality",
"verbose_response": "Here is my review",
"review_date": "2017-06-28T14:54:27.937735",
"status": "active"
}
]
e) Not In clause:
API End Point : http://localhost:2020/t_review_response?response_id=not.in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
API Response:
[
{
"response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
Note : documentation of postgrest list way to use other common operators like "less than equality","greator than" etc.
Using Views in Postgrest:
Postgrest can also RESTIFY views.Lets create a sample view like below
CREATE VIEW vw_review_response AS
SELECT
t_review.review_headline,
t_review_response.review_id,
count(*) totalResponses,
sum(t_review_response.vote) as votes ,
sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
FROM
t_review
LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id
GROUP BY
t_review_response.review_id,
t_review.review_headline;
GRANT SELECT ON TABLE vw_review_response TO mypgusername;
API End Point:http://localhost:2020/vw_review_response
API Response:
[
{
"review_headline": "LG V20 battery backup",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"totalresponses": 128,
"votes": 2,
"totalupvotes": 65,
"totaldownvotes": 63
},
{
"review_headline": "LG V20 Speaker Sound Quality",
"review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
"totalresponses": 64,
"votes": 128,
"totalupvotes": 64,
"totaldownvotes": 0
}
]
Select Specific Columns:
We can also select only specific columns from table.In below request we will read 3 columns only, namely review_headline,totalupvotes,totaldownvotes.
API End Point:http://localhost:2020/vw_review_response?select=review_headline,totalupvotes,totaldownvotes
API Response:
[
{
"review_headline": "LG V20 battery backup",
"totalupvotes": 65,
"totaldownvotes": 63
},
{
"review_headline": "LG V20 Speaker Sound Quality",
"totalupvotes": 64,
"totaldownvotes": 0
}
]
Ordering Rows:
we can order the result set using order param,Here our sort order is totalupvotes ascending & totaldownvotes descending.
API End Point:http://localhost:2020/vw_review_response?order=totalupvotes.asc,totaldownvotes.desc
API Response:
[
{
"review_headline": "LG V20 Speaker Sound Quality",
"review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
"totalresponses": 64,
"votes": 128,
"totalupvotes": 64,
"totaldownvotes": 0
},
{
"review_headline": "LG V20 battery backup",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"totalresponses": 128,
"votes": 2,
"totalupvotes": 65,
"totaldownvotes": 63
}
]
Limiting Numbers of rows in Result set:
In Postgrest you can add header in http request to limit number of rows as follows
API End point:http://localhost:2020/t_review_response
Request Header:
{
Range : 0-4
}
API Response:
[
{
"response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote": 1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
Note:The Range:0-4 gives five rows the counting begins at zero.
To know total number of record along with effect of server side pagination. we need to have additional header
{
Prefer:count=exact
}
we are using CURL tool on linux terminal to test the functionality as below.
Request: curl -i -H "Range:0-4" -H "Prefer:count=exact" http://localhost:2020/t_review_response
Response:
HTTP/1.1 206 Partial Content
Transfer-Encoding: chunked
Date: Wed, 28 Jun 2017 13:40:21 GMT
Server: postgrest/0.3.1.0
Content-Type: application/json
Content-Range: 0-4/192
Content-Location: /t_review_response
[
{
"response_id":"cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote":1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"d3f5841d-6d38-47a4-a60f-a9cf17653574",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"fb8cfa0d-896a-44aa-9530-7517b9e5259f",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"ab7d479b-36ae-4b03-b23a-f883939e11ec",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"40baf601-ab30-4f07-88f2-9b9f51d30c76",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
}
]
In response header we got "Content-Range" as "0-4/192" that says that there are in all 192 record out of which we are selecting 5 records from 0th till 4th.
samething can be done using postman to see response headers beside body & cookie tabs.
Calling Stored Procedure:
To demonstrate how we can call stored procedure through postgrest we will create a sample Stored procedure as follows
CREATE OR REPLACE FUNCTION fn_get_review_response(p_review_id uuid)
RETURNS TABLE (headline varchar, review_id uuid,totalresponses bigint,votes bigint,totalupvotes bigint,totaldownvotes bigint)
AS $$
BEGIN
RETURN QUERY
SELECT
t_review.review_headline,
t_review_response.review_id,
count(*) totalResponses,
sum(t_review_response.vote) as votes ,
sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
FROM
t_review
LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id
WHERE
t_review_response.review_id = p_review_id
GROUP BY
t_review_response.review_id,
t_review.review_headline;
END
$$ LANGUAGE plpgsql;
may need to grant access to user through which postgrest is being run
GRANT EXECUTE ON FUNCTION fn_get_review_response(uuid) TO mypgusername
Now we can call our stored procedure,stored procedures are exposed in postgrest under "/rpc".
You can use "Postman" extension to test POST API.
API End Point :
http://localhost:2020/rpc/fn_get_review_response
Header:
Content-Type : application/json
Payload:
{
"p_review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9"
}
Note: Here I observed that JSON key ,need to be put into quotes else end up in error.
API Response:
[
{
"totalupvotes": 65,
"votes": 2,
"headline": "LG V20 battery backup",
"totalresponses": 128,
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"totaldownvotes": 63
}
]
We need to explore built in security aspect of Postgrest further.
First we will download postgrest(https://github.com/begriffs/postgrest/releases/tag/v0.3.1.0). For my debian machine I am using ubuntu version of postgrest (postgrest-0.3.1.0-ubuntu.tar.xz),Extract it and rename it to just "postgrest",You can preferably move it to "/opt/".
We can run it as follows from Linux Terminal:
cd /opt
./postgrest 'postgres://mypgusername:mypassword@localhost:5432/mypgdb' -a mypgusername -p 2020
Note: Here first argument is jdbc url to our postgres server."mypgusername" is database role & 2020 is port on which Postgrest will run.
role can be "anonymous".
We need some tables to check Postgrest functionality.Lets create below table
create table t_review
(
review_id uuid,
user_id uuid,
product_id uuid,
review_headline varchar,
verbose_response text,
review_date timestamp without time zone,
status record_status
)
create table t_review_response
(
response_id uuid,
review_id uuid,
user_id uuid,
vote smallint,
vote_date timestamp without time zone,
status record_status
)
Add some dummy data in both tables please keep in mind "review_id" in "t_review_response" should belong to "t_review"
You may need to give access right to this table to user through which postgrest is running
GRANT SELECT ON TABLE t_review TO mypgusername;
GRANT SELECT,INSERT,UPDATE ON playground.public.t_review_response to mypgusername;
As some columns are declared of type uuid,to generate new uuid in postgres we might need to add corresponding extension if not already installed.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Now we can read data from any table using GET as follows
API End Point :http://localhost:2020/t_review_response
API Response:
[
{
"response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote": 1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
Where Clause:
a) Equality:
API End Point :http://localhost:2020/t_review_response?vote=eq.-1&review_id=eq.8e03e047-7eea-4725-8e5d-5c3dded7c0b9&response_id=eq.75320cf9-e5ca-4844-a2ae-ae6a44466fa9
API Response:
[
{
"response_id": "75320cf9-e5ca-4844-a2ae-ae6a44466fa9",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "c7de1c3d-0172-44f2-ad92-88bb9eed1918",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
b) In clause:
API End Point :http://localhost:2020/t_review_response?response_id=in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
API Response:
[
{
"response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote": 1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
c) like Clause:
API End Point :http://localhost:2020/t_review?review_headline=like.*battery*
API Response:
[
{
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "4ace0309-b355-403f-9777-f046c157a9b0",
"product_id": "be4b2c18-3afb-41ec-9a6b-157be393eb03",
"review_headline": "LG V20 battery backup",
"verbose_response": "Here is my review",
"review_date": "2017-06-28T14:53:46.974368",
"status": "active"
}
]
Postgrest uses * instead of % in like queries unlike sql queries.
d) not like clause:
API End Point : http://localhost:2020/t_review?review_headline=not.like.*battery*
API Response:
[
{
"review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
"user_id": "7b326e04-3f12-45a6-9ec2-1015491fccce",
"product_id": "50514530-e1a7-4904-b4f4-4244d3579658",
"review_headline": "LG V20 Speaker Sound Quality",
"verbose_response": "Here is my review",
"review_date": "2017-06-28T14:54:27.937735",
"status": "active"
}
]
e) Not In clause:
API End Point : http://localhost:2020/t_review_response?response_id=not.in.cc19414f-449d-447b-a1ae-d741f2f4e05d,e18905d4-5f34-4060-b7fc-fd919849e6ed
API Response:
[
{
"response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
Note : documentation of postgrest list way to use other common operators like "less than equality","greator than" etc.
Using Views in Postgrest:
Postgrest can also RESTIFY views.Lets create a sample view like below
CREATE VIEW vw_review_response AS
SELECT
t_review.review_headline,
t_review_response.review_id,
count(*) totalResponses,
sum(t_review_response.vote) as votes ,
sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
FROM
t_review
LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id
GROUP BY
t_review_response.review_id,
t_review.review_headline;
GRANT SELECT ON TABLE vw_review_response TO mypgusername;
API End Point:http://localhost:2020/vw_review_response
API Response:
[
{
"review_headline": "LG V20 battery backup",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"totalresponses": 128,
"votes": 2,
"totalupvotes": 65,
"totaldownvotes": 63
},
{
"review_headline": "LG V20 Speaker Sound Quality",
"review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
"totalresponses": 64,
"votes": 128,
"totalupvotes": 64,
"totaldownvotes": 0
}
]
Select Specific Columns:
We can also select only specific columns from table.In below request we will read 3 columns only, namely review_headline,totalupvotes,totaldownvotes.
API End Point:http://localhost:2020/vw_review_response?select=review_headline,totalupvotes,totaldownvotes
API Response:
[
{
"review_headline": "LG V20 battery backup",
"totalupvotes": 65,
"totaldownvotes": 63
},
{
"review_headline": "LG V20 Speaker Sound Quality",
"totalupvotes": 64,
"totaldownvotes": 0
}
]
Ordering Rows:
we can order the result set using order param,Here our sort order is totalupvotes ascending & totaldownvotes descending.
API End Point:http://localhost:2020/vw_review_response?order=totalupvotes.asc,totaldownvotes.desc
API Response:
[
{
"review_headline": "LG V20 Speaker Sound Quality",
"review_id": "016b0891-5411-4f40-b0f9-c6a036f309f9",
"totalresponses": 64,
"votes": 128,
"totalupvotes": 64,
"totaldownvotes": 0
},
{
"review_headline": "LG V20 battery backup",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"totalresponses": 128,
"votes": 2,
"totalupvotes": 65,
"totaldownvotes": 63
}
]
Limiting Numbers of rows in Result set:
In Postgrest you can add header in http request to limit number of rows as follows
API End point:http://localhost:2020/t_review_response
Request Header:
{
Range : 0-4
}
API Response:
[
{
"response_id": "cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote": 1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "d3f5841d-6d38-47a4-a60f-a9cf17653574",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "fb8cfa0d-896a-44aa-9530-7517b9e5259f",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "ab7d479b-36ae-4b03-b23a-f883939e11ec",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "40baf601-ab30-4f07-88f2-9b9f51d30c76",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
},
{
"response_id": "b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id": "1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
"vote": -1,
"vote_date": "2017-06-28T14:36:36.206623",
"status": "active"
}
]
Note:The Range:0-4 gives five rows the counting begins at zero.
To know total number of record along with effect of server side pagination. we need to have additional header
{
Prefer:count=exact
}
we are using CURL tool on linux terminal to test the functionality as below.
Request: curl -i -H "Range:0-4" -H "Prefer:count=exact" http://localhost:2020/t_review_response
Response:
HTTP/1.1 206 Partial Content
Transfer-Encoding: chunked
Date: Wed, 28 Jun 2017 13:40:21 GMT
Server: postgrest/0.3.1.0
Content-Type: application/json
Content-Range: 0-4/192
Content-Location: /t_review_response
[
{
"response_id":"cc19414f-449d-447b-a1ae-d741f2f4e05d",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"9b7f5b2d-b35a-4a53-bd50-6b0a69784dad",
"vote":1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"e18905d4-5f34-4060-b7fc-fd919849e6ed",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"9dd246a6-cf01-4df5-b259-68ca153bdda9",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"d3f5841d-6d38-47a4-a60f-a9cf17653574",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"fb8cfa0d-896a-44aa-9530-7517b9e5259f",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"ab7d479b-36ae-4b03-b23a-f883939e11ec",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"40baf601-ab30-4f07-88f2-9b9f51d30c76",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
},
{
"response_id":"b772deb7-5b5b-4db8-966a-d7ec8b14aceb",
"review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"user_id":"1900cbb5-6b1f-4cf4-b7d8-ecbc9bce3e42",
"vote":-1,
"vote_date":"2017-06-28T14:36:36.206623",
"status":"active"
}
]
In response header we got "Content-Range" as "0-4/192" that says that there are in all 192 record out of which we are selecting 5 records from 0th till 4th.
samething can be done using postman to see response headers beside body & cookie tabs.
Calling Stored Procedure:
To demonstrate how we can call stored procedure through postgrest we will create a sample Stored procedure as follows
CREATE OR REPLACE FUNCTION fn_get_review_response(p_review_id uuid)
RETURNS TABLE (headline varchar, review_id uuid,totalresponses bigint,votes bigint,totalupvotes bigint,totaldownvotes bigint)
AS $$
BEGIN
RETURN QUERY
SELECT
t_review.review_headline,
t_review_response.review_id,
count(*) totalResponses,
sum(t_review_response.vote) as votes ,
sum(case when t_review_response.vote > 0 then 1 else 0 end) as totalUpVotes,
sum(case when t_review_response.vote < 0 then 1 else 0 end) as totalDownVotes
FROM
t_review
LEFT OUTER JOIN t_review_response on t_review.review_id = t_review_response.review_id
WHERE
t_review_response.review_id = p_review_id
GROUP BY
t_review_response.review_id,
t_review.review_headline;
END
$$ LANGUAGE plpgsql;
may need to grant access to user through which postgrest is being run
GRANT EXECUTE ON FUNCTION fn_get_review_response(uuid) TO mypgusername
Now we can call our stored procedure,stored procedures are exposed in postgrest under "/rpc".
You can use "Postman" extension to test POST API.
API End Point :
http://localhost:2020/rpc/fn_get_review_response
Header:
Content-Type : application/json
Payload:
{
"p_review_id":"8e03e047-7eea-4725-8e5d-5c3dded7c0b9"
}
Note: Here I observed that JSON key ,need to be put into quotes else end up in error.
API Response:
[
{
"totalupvotes": 65,
"votes": 2,
"headline": "LG V20 battery backup",
"totalresponses": 128,
"review_id": "8e03e047-7eea-4725-8e5d-5c3dded7c0b9",
"totaldownvotes": 63
}
]
We need to explore built in security aspect of Postgrest further.
No comments:
Post a Comment