forked from PierreSenellart/provsql
-
Notifications
You must be signed in to change notification settings - Fork 0
/
where_provenance.sql
43 lines (37 loc) · 1.05 KB
/
where_provenance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
\set ECHO none
SET search_path TO public, provsql;
/* Example of where-provenance */
CREATE TABLE result_where AS
SELECT p1.city AS c1, p2.city AS c2,
regexp_replace(where_provenance(provenance()),':[0-9a-f-]*:','::','g')
FROM personnel p1, personnel p2
WHERE p1.city = p2.city AND p1.id < p2.id
GROUP BY p1.city, p2.city
ORDER BY p1.city;
SELECT remove_provenance('result_where');
SELECT * FROM result_where;
DROP TABLE result_where;
CREATE TABLE result_where AS
SELECT city,
regexp_replace(where_provenance(provenance()),':[0-9a-f-]*:','::','g')
FROM (
SELECT DISTINCT p1.city
FROM personnel p1, personnel p2
WHERE p2.city='Paris'
) t
ORDER BY city;
SELECT remove_provenance('result_where');
SELECT * FROM result_where;
DROP TABLE result_where;
CREATE TABLE result_where AS
SELECT 1,
city,
regexp_replace(where_provenance(provenance()),':[0-9a-f-]*:','::','g')
FROM (
SELECT city
FROM personnel
) t
ORDER BY city;
SELECT remove_provenance('result_where');
SELECT * FROM result_where;
DROP TABLE result_where;