Before reading this blog, you must know the basics of Graph database and Cypher
if you are not familiar with that you can read these blogs
Introduction
To assist in querying graph DB, Stored Procedure can be used using the PL/pgSQL language provided by PostgreSQL.
You can view and handle graph data more efficiently.
AgensGraph
Agens Graph is a multi-model graph database fork of PostgreSQL. PostgreSQL introduced Stored Procedure for better productivity and performance.
Stored Procedures
Stored Procedure uses PL/pgSQL language to provide fast response and convenience, as well as the foundation for extending the functionality of PostgreSQL itself.
Stored Procedure can help query variables used in Cypher queries or convert variables into types.
Basic Procedure Structure
CREATE Function function_name (parameter1 type, parameter2 type)
RETURNS return_type AS
BEGIN
[CODE]
END;
LANGUAGE language_name;
Procedure can be created with CREATE Function statement,
Specifies the name and factor of the function, the type of factor, and the type of return factor.
Between BEGIN and END, write the code for the function to execute.
Use LANGUAGE to specify the language used in the function instead of language_name.
Find Fraud Ring (An Example)
Find out how to use Procedure through the public fraud graph.
- Find out how to use Procedure in practice through the insurance fraud graph we checked earlier.
- Fraud Ring is an example of a cycle structure that occurs between two or more criminals during a public fraud.
- Traditional graph pattern searches have a large number of cases or complicatedly intertwined, making them difficult to recognize at a glance and limited in obtaining the desired results.
- With the help of Stored Procedure, let's explore Path to avoid duplicating the ID of the Vertex that makes up the Fraud Ring.
Use the conspiracy fraud graph to learn how to use Procedure.
- First, you need to look up the Graph Path of the Cycle structure passing through the specified vertex.
- You can query any path that contains it by specifying the specified Vertex as a query in the Cypher language.
- Duplicate Paths may exist because they are output without considering bidirectional edges.
- The unique Vertex ID can be retrieved from the path inquired through Stored Procedure to exclude cases where the same path appears.
Create Graph
Paste the following Code in AGCloud's AGviewer to Create the Graph
CREATE(Male101:person{Name:'Male101', Gender:'Male', Age:'37', Residence:'Philadelphia'})
CREATE(Female101:person{Name:'Female101', Gender:'Female', Age:'39',Residence:'Houston'})
CREATE(Male102:person{Name:'Male102', Gender:'Male', Age:'45',Residence:'San Francisco'})
CREATE(Male103:person{Name:'Male103', Gender:'Male', Age:'41',Residence:'San Francisco'})
CREATE(Female102:person{Name:'Female102', Gender:'Female', Age:'35',Residence:'New York'})
CREATE(Male104:person{Name:'Male104', Gender:'Male', Age:'29',Residence:'New York'})
CREATE(Male105:person{Name:'Male105', Gender:'Male', Age:'51',Residence:'Chicago'})
CREATE(Female103:person{Name:'Female103', Gender:'Female', Age:'27',Residence:'New York'})
CREATE(Male106:person{Name:'Male106', Gender:'Male', Age:'29', Residence:'New York'})
CREATE(Male107:person{Name:'Male107', Gender:'Male', Age:'30', Residence:'New York'})
CREATE(Male108:person{Name:'Male108', Gender:'Male', Age:'31', Residence:'New York'})
CREATE(Male109:person{Name:'Male109', Gender:'Male', Age:'25', Residence:'New York'})
CREATE(Male110:person{Name:'Male110', Gender:'Male', Age:'25', Residence:'New York'})
CREATE(Male111:person{Name:'Male111', Gender:'Male', Age:'28', Residence:'New York'})
CREATE(Male112:person{Name:'Male112', Gender:'Male', Age:'33', Residence:'New York'})
CREATE(Male113:person{Name:'Male113', Gender:'Male', Age:'53', Residence:'New York'})
CREATE(Male114:person{Name:'Male114', Gender:'Male', Age:'22', Residence:'New York'})
CREATE(Male115:person{Name:'Male115', Gender:'Male', Age:'29', Residence:'Chicago'})
CREATE(Male116:person{Name:'Male116', Gender:'Male', Age:'46', Residence:'Chicago'})
CREATE(Male117:person{Name:'Male117', Gender:'Male', Age:'27', Residence:'Chicago'})
CREATE(Male118:person{Name:'Male118', Gender:'Male', Age:'27', Residence:'Chicago'})
CREATE(Male119:person{Name:'Male119', Gender:'Male', Age:'49', Residence:'San Francisco'})
CREATE(Male120:person{Name:'Male120', Gender:'Male', Age:'23', Residence:'San Francisco'})
CREATE(Male121:person{Name:'Male121', Gender:'Male', Age:'37', Residence:'San Francisco'})
CREATE(Male122:person{Name:'Male122', Gender:'Male', Age:'35', Residence:'San Francisco'})
CREATE(Male123:person{Name:'Male123', Gender:'Male', Age:'42', Residence:'San Francisco'})
CREATE(Male124:person{Name:'Male124', Gender:'Male', Age:'29', Residence:'San Francisco'})
CREATE(Male125:person{Name:'Male125', Gender:'Male', Age:'28', Residence:'San Francisco'})
CREATE(Male126:person{Name:'Male126', Gender:'Male', Age:'30', Residence:'San Francisco'})
CREATE(Male127:person{Name:'Male127', Gender:'Male', Age:'46', Residence:'San Francisco'})
CREATE(Male128:person{Name:'Male128', Gender:'Male', Age:'46', Residence:'San Francisco'})
CREATE(Male129:person{Name:'Male129', Gender:'Male', Age:'23', Residence:'San Francisco'})
CREATE(Male130:person{Name:'Male130', Gender:'Male', Age:'42', Residence:'San Francisco'})
CREATE(Female104:person{Name:'Female104', Gender:'Female', Age:'34', Residence:'New York'})
CREATE(Female105:person{Name:'Female105', Gender:'Female', Age:'38', Residence:'New York'})
CREATE(Female106:person{Name:'Female106', Gender:'Female', Age:'56', Residence:'New York'})
CREATE(Female107:person{Name:'Female107', Gender:'Female', Age:'28', Residence:'New York'})
CREATE(Female108:person{Name:'Female108', Gender:'Female', Age:'30', Residence:'New York'})
CREATE(Female109:person{Name:'Female109', Gender:'Female', Age:'41', Residence:'New York'})
CREATE(Female110:person{Name:'Female110', Gender:'Female', Age:'21', Residence:'New York'})
CREATE(Female111:person{Name:'Female111', Gender:'Female', Age:'56', Residence:'New York'})
CREATE(Female112:person{Name:'Female112', Gender:'Female', Age:'44', Residence:'New York'})
CREATE(Female113:person{Name:'Female113', Gender:'Female', Age:'42', Residence:'San Francisco'})
CREATE(Female114:person{Name:'Female114', Gender:'Female', Age:'21', Residence:'San Francisco'})
CREATE(Female115:person{Name:'Female115', Gender:'Female', Age:'28', Residence:'San Francisco'})
CREATE(Female116:person{Name:'Female116', Gender:'Female', Age:'58', Residence:'San Francisco'})
CREATE(Female117:person{Name:'Female117', Gender:'Female', Age:'42', Residence:'San Francisco'})
CREATE(Female118:person{Name:'Female118', Gender:'Female', Age:'49', Residence:'San Francisco'})
CREATE(Female119:person{Name:'Female119', Gender:'Female', Age:'51', Residence:'Chicago'})
CREATE(Female120:person{Name:'Female120', Gender:'Female', Age:'27', Residence:'Chicago'})
-- CREATE CARS
CREATE(N1111:car{Car_num:'1111',Manufacturer:'Hyundai-Kia', Name:'K5'})
CREATE(N1234:car{Car_num:'1234',Manufacturer:'Ford', Name:'Explorer'})
CREATE(N1512:car{Car_num:'1512',Manufacturer:'Hyundai-Kia', Name:'Sorento'})
CREATE(N1616:car{Car_num:'1616',Manufacturer:'Volkswagen', Name:'POLO'})
CREATE(N1717:car{Car_num:'1717',Manufacturer:'Chevrole', Name:'Cruze'})
CREATE(N1712:car{Car_num:'1712',Manufacturer:'BMW', Name:'BMW520'})
CREATE(N2706:car{Car_num:'2706',Name:'The BEETLE',Manufacturer:'Volkswagen'})
CREATE(N5793:car{Car_num:'5793',Name:'Scirocco',Manufacturer:'Volkswagen'})
CREATE(N4278:car{Car_num:'4278',Name:'JETTA' ,Manufacturer:'Volkswagen'})
CREATE(N5241:car{Car_num:'5241',Name:'CELTOS',Manufacturer:'Hyundai-Kia'})
CREATE(N4176:car{Car_num:'4176',Name:'Kuga',Manufacturer:'Ford'})
CREATE(N6512:car{Car_num:'6512',Name:'GOLF',Manufacturer:'Volkswagen'})
CREATE(N5595:car{Car_num:'5595',Name:'A3',Manufacturer:'Audi'})
CREATE(N8523:car{Car_num:'8523',Name:'Sportage',Manufacturer:'Hyundai-Kia'})
CREATE(N4332:car{Car_num:'4332',Name:'Mustang',Manufacturer:'Ford'})
CREATE(N9908:car{Car_num:'9908',Name:'POLO',Manufacturer:'Volkswagen'})
CREATE(N5761:car{Car_num:'5761',Name:'K5',Manufacturer:'Hyundai-Kia'})
CREATE(N1679:car{Car_num:'1679',Name:'Cruze',Manufacturer:'Chevrole'})
CREATE(N9942:car{Car_num:'9942',Name:'A3',Manufacturer:'Audi'})
CREATE(N9150:car{Car_num:'9150',Name:'GOLF',Manufacturer:'Volkswagen'})
CREATE(N8644:car{Car_num:'8644',Name:'POLO',Manufacturer:'Volkswagen'})
CREATE(N6712:car{Car_num:'6712',Name:'K5',Manufacturer:'Hyundai-Kia'})
CREATE(N8334:car{Car_num:'8334',Name:'POLO' ,Manufacturer:'Volkswagen'})
CREATE(N9330:car{Car_num:'9330',Name:'K5',Manufacturer:'Hyundai-Kia'})
CREATE(N3454:car{Car_num:'3454',Name:'Morning',Manufacturer:'Hyundai-Kia'})
CREATE(N7236:car{Car_num:'7236',Name:'Morning',Manufacturer:'Hyundai-Kia'})
CREATE(N8076:car{Car_num:'8076',Name:'Ray',Manufacturer:'Hyundai-Kia'})
CREATE(N7445:car{Car_num:'7445',Name:'BMW520',Manufacturer:'BMW'})
-- CREATE ACCIDENTS
CREATE (ACC19111:accident {Name:'19111', accident_date:'19/05/2020', City:'New York'})
CREATE (ACC19222:accident {Name:'19222', accident_date:'23/05/2020', City:'Chicago'})
CREATE (ACC19333:accident {Name:'19333', accident_date:'27/05/2020', City:'Jacksonville'})
CREATE (ACC19292:accident {Name:'19292', accident_date:'11/05/2020',City:'New York'})
CREATE (ACC19419:accident {Name:'19419', accident_date:'13/05/2020',City:'Chicago'})
CREATE (ACC19314:accident {Name:'19314', accident_date:'08/05/2020',City:'Jacksonville'})
CREATE (ACC19400:accident {Name:'19400', accident_date:'10/05/2020',City:'Dallas'})
CREATE (ACC19830:accident {Name:'19830', accident_date:'16/05/2020',City:'Phoenix'})
CREATE (ACC19706:accident {Name:'19706', accident_date:'16/05/2020',City:'San Diego'})
CREATE (ACC19666:accident {Name:'19666', accident_date:'14/05/2020',City:'San Jose'})
CREATE (ACC19104:accident {Name:'19104', accident_date:'03/05/2020',City:'New York'})
CREATE (ACC19430:accident {Name:'19430', accident_date:'10/05/2020',City:'Dallas'})
CREATE (ACC19223:accident {Name:'19223', accident_date:'22/05/2020',City:'Jacksonville'})
CREATE (ACC19565:accident {Name:'19565', accident_date:'14/05/2020',City:'Chicago'})
-- CREATE CARS_ACCIDENTS EDGES
CREATE (N1111)-[:IS_INVOLVED]->(ACC19111)
MERGE (N1234)-[:IS_INVOLVED]->(ACC19111)
MERGE (N1512)-[:IS_INVOLVED]->(ACC19222)
MERGE (N1616)-[:IS_INVOLVED]->(ACC19222)
MERGE (N1717)-[:IS_INVOLVED]->(ACC19333)
MERGE (N1712)-[:IS_INVOLVED]->(ACC19333)
MERGE (N2706)-[:IS_INVOLVED]->(ACC19292)
MERGE (N5793)-[:IS_INVOLVED]->(ACC19292)
MERGE (N4278)-[:IS_INVOLVED]->(ACC19419)
MERGE (N5241)-[:IS_INVOLVED]->(ACC19419)
MERGE (N4176)-[:IS_INVOLVED]->(ACC19314)
MERGE (N6512)-[:IS_INVOLVED]->(ACC19314)
MERGE (N5595)-[:IS_INVOLVED]->(ACC19400)
MERGE (N8523)-[:IS_INVOLVED]->(ACC19400)
MERGE (N4332)-[:IS_INVOLVED]->(ACC19830)
MERGE (N9908)-[:IS_INVOLVED]->(ACC19830)
MERGE (N5761)-[:IS_INVOLVED]->(ACC19706)
MERGE (N1679)-[:IS_INVOLVED]->(ACC19706)
MERGE (N9942)-[:IS_INVOLVED]->(ACC19666)
MERGE (N9150)-[:IS_INVOLVED]->(ACC19666)
MERGE (N8644)-[:IS_INVOLVED]->(ACC19104)
MERGE (N6712)-[:IS_INVOLVED]->(ACC19104)
MERGE (N2706)-[:IS_INVOLVED]->(ACC19292)
MERGE (N5793)-[:IS_INVOLVED]->(ACC19292)
MERGE (N4278)-[:IS_INVOLVED]->(ACC19419)
MERGE (N5241)-[:IS_INVOLVED]->(ACC19419)
MERGE (N4176)-[:IS_INVOLVED]->(ACC19314)
MERGE (N6512)-[:IS_INVOLVED]->(ACC19314)
MERGE (N5595)-[:IS_INVOLVED]->(ACC19400)
MERGE (N8523)-[:IS_INVOLVED]->(ACC19400)
MERGE (N4332)-[:IS_INVOLVED]->(ACC19830)
MERGE (N8334)-[:IS_INVOLVED]->(ACC19430)
MERGE (N9330)-[:IS_INVOLVED]->(ACC19430)
MERGE (N3454)-[:IS_INVOLVED]->(ACC19223)
MERGE (N7236)-[:IS_INVOLVED]->(ACC19223)
MERGE (N8076)-[:IS_INVOLVED]->(ACC19565)
MERGE (N7445)-[:IS_INVOLVED]->(ACC19565)
MERGE (N9908)-[:IS_INVOLVED]->(ACC19830)
MERGE (N5761)-[:IS_INVOLVED]->(ACC19706)
MERGE (N1679)-[:IS_INVOLVED]->(ACC19706)
MERGE (N9942)-[:IS_INVOLVED]->(ACC19666)
MERGE (N9150)-[:IS_INVOLVED]->(ACC19666)
MERGE (N8644)-[:IS_INVOLVED]->(ACC19430)
MERGE (N6712)-[:IS_INVOLVED]->(ACC19430)
MERGE (N2706)-[:IS_INVOLVED]->(ACC19223)
MERGE (N5793)-[:IS_INVOLVED]->(ACC19223)
MERGE (N4278)-[:IS_INVOLVED]->(ACC19565)
MERGE (N5241)-[:IS_INVOLVED]->(ACC19565)
CREATE (Male101)-[:DRIVER]->(N1111)
CREATE (Male101)-[:PASSENGER]->(N1512)
CREATE (Male101)-[:PASSENGER]->(N1717)
CREATE (Female101)-[:DRIVER]->(N1234)
CREATE (Female101)-[:PASSENGER]->(N1616)
CREATE (Female101)-[:PASSENGER]->(N1712)
CREATE (Male102)-[:DRIVER]->(N1512)
CREATE (Male102)-[:PASSENGER]->(N1111)
CREATE (Male102)-[:PASSENGER]->(N1712)
CREATE (Male103)-[:DRIVER]->(N1616)
CREATE (Male103)-[:PASSENGER]->(N1717)
CREATE (Male103)-[:PASSENGER]->(N1234)
CREATE (Female102)-[:DRIVER]->(N1717)
CREATE (Female102)-[:PASSENGER]->(N1616)
CREATE (Female102)-[:PASSENGER]->(N1111)
CREATE (Male104)-[:DRIVER]->(N1712)
CREATE (Male104)-[:PASSENGER]->(N1234)
CREATE (Male104)-[:PASSENGER]->(N1512)
CREATE (Male105)-[:IS_DOCTOR]->(Male101)
CREATE (Male105)-[:IS_DOCTOR]->(Male102)
CREATE (Male105)-[:IS_DOCTOR]->(Female102)
CREATE (Female103)-[:IS_INSU_AGENT]->(Female101)
CREATE (Female103)-[:IS_INSU_AGENT]->(Male102)
CREATE (Female103)-[:IS_INSU_AGENT]->(Male103)
CREATE (Female103)-[:IS_INSU_AGENT]->(Female102)
CREATE (Male106)-[:DRIVER]->(N2706)
CREATE (Male107)-[:DRIVER]->(N5793)
CREATE (Male108)-[:DRIVER]->(N4278)
CREATE (Male109)-[:DRIVER]->(N5241)
CREATE (Male110)-[:DRIVER]->(N4176)
CREATE (Male111)-[:DRIVER]->(N6512)
CREATE (Male112)-[:DRIVER]->(N5595)
CREATE (Male113)-[:DRIVER]->(N8523)
CREATE (Male114)-[:DRIVER]->(N4332)
CREATE (Male115)-[:DRIVER]->(N9908)
CREATE (Male116)-[:DRIVER]->(N5761)
CREATE (Male117)-[:DRIVER]->(N1679)
CREATE (Male118)-[:DRIVER]->(N9942)
CREATE (Male119)-[:DRIVER]->(N9150)
CREATE (Male120)-[:DRIVER]->(N8644)
CREATE (Male121)-[:DRIVER]->(N6712)
CREATE (Male122)-[:PASSENGER]->(N2706)
CREATE (Male123)-[:PASSENGER]->(N5793)
CREATE (Male124)-[:PASSENGER]->(N4278)
CREATE (Male125)-[:PASSENGER]->(N5241)
CREATE (Male126)-[:PASSENGER]->(N4176)
CREATE (Male127)-[:PASSENGER]->(N6512)
CREATE (Male128)-[:PASSENGER]->(N5595)
CREATE (Male129)-[:PASSENGER]->(N8523)
CREATE (Male130)-[:PASSENGER]->(N4332)
CREATE (Female104)-[:DRIVER]->(N8334)
CREATE (Female105)-[:DRIVER]->(N9330)
CREATE (Female106)-[:DRIVER]->(N3454)
CREATE (Female107)-[:DRIVER]->(N7236)
CREATE (Female108)-[:DRIVER]->(N8076)
CREATE (Female109)-[:DRIVER]->(N7445)
CREATE (Female110)-[:PASSENGER]->(N9908)
CREATE (Female111)-[:PASSENGER]->(N5761)
CREATE (Female112)-[:PASSENGER]->(N1679)
CREATE (Female113)-[:PASSENGER]->(N9942)
CREATE (Female114)-[:PASSENGER]->(N9150)
CREATE (Female115)-[:PASSENGER]->(N8644)
CREATE (Female116)-[:PASSENGER]->(N6712)
CREATE (Female117)-[:PASSENGER]->(N2706)
CREATE (Female118)-[:PASSENGER]->(N5793)
CREATE (Female119)-[:PASSENGER]->(N4278)
CREATE (Female120)-[:PASSENGER]->(N5241);
To be Continued in Next Tutorial
Top comments (0)