Overview

The CALL statement allows you to execute a procedure, either on its own or as part of a larger query. A procedure can either be system-defined (referred to as a named procedure) or manually defined within the query (known as an inline procedure).

<call procedure statement> ::= [ "OPTIONAL" ] "CALL" <procedure call>
  
<procedure call> ::= <named procedure call> | <inline procedure call>

Details

• The OPTIONAL keyword can be used to handle cases where the procedure might not return results. If no result is found, it produces null values instead.

Example Graph

The following examples run against this graph:

To create this graph, run the following query against an empty graph:

INSERT (rowlock:User {_id:'U01', name:'rowlock'}),
       (brainy:User {_id:'U02', name:'Brainy'}),
       (purplechalk:User {_id:'U03', name:'purplechalk'}),
       (mochaeach:User {_id:'U04', name:'mochaeach'}),
       (lionbower:User {_id:'U05', name:'lionbower'}),
       (c01:Club {_id:'C01'}),
       (c02:Club {_id:'C02'}),
       (rowlock)-[:Follows]->(brainy),
       (mochaeach)-[:Follows]->(brainy),
       (brainy)-[:Follows]->(purplechalk),
       (purplechalk)-[:Follows]->(brainy),
       (lionbower)-[:Follows]->(purplechalk),
       (brainy)-[:Joins]->(c01),
       (lionbower)-[:Joins]->(c01),
       (mochaeach)-[:Joins]->(c02)

Named Procedure Calls

The CALL statement can invoke a pre-defined procedure deployed in the database. Ultipa offers a range of built-in procedures.

<named procedure call> ::=
  <procedure reference> "(" [ <procedure argument list> ] ")" [ <yield clause> ]

<procedure argument list> ::=
  <procedure argument> [ { "," <procedure argument> }... ]

<procedure argument> ::= <value expression>

Details

• The YIELD clause can be used to select and rename columns of the binding table (if has) produced by the named procedure, allowing them to be exposed and referenced in subsequent parts of the query.

Creating HDC Graph

This query calls the procedure hdc.graph.create to project the whole current graph as a HDC graph hdc_g1 on HDC server hdc-server-1:

CALL hdc.graph.create("hdc-server-1", "hdc_g1", {
    nodes: {"*": ["*"]},
    edges: {"*": ["*"]},
    query: "query",
    type: "Graph",
    update: "static",
    default: true
})

Showing HDC Graph

This query calls the procedure hdc.graph.show to retrieve all HDC graphs of the current graph:

CALL hdc.graph.show()

Result: _projectList

This query calls the procedure hdc.graph.show to retrieve the HDC graph hdc_g1 of the current graph:

CALL hdc.graph.show("hdc_g1")

Dropping HDC Graph

This query calls the procedure hdc.graph.drop to drop the HDC graph hdc_g2 of the current graph:

CALL hdc.graph.drop("hdc_g2")

Showing Job

This query calls the procedure job.show to retrieve all jobs in the current graph:

CALL job.show()

Result: result

This query calls the procedure job.show to retrieve the job with job_id as 1 in the current graph:

CALL job.show("1")

Running HDC Algorithm

This query calls the procedure hdc.algo.degree to run the Degree Centrality algorithm on the HDC graph hdc_g1:

call hdc.algo.degree("hdc_g1", {
  order: "desc"
}) YIELD r
RETURN r

Result: r

Running HDC Algorithm with Writeback

This query calls the procedure hdc.algo.degree.write to run the Degree Centrality algorithm on the HDC graph hdc_g1 and write the results back to file degree.txt:

CALL hdc.algo.degree.write("hdc_g1", {
    params: {
        order: "desc"
    },
    return_params: {
        file: {
          filename: "degree.txt"
        }
    }
})

Inline Procedure Calls

<inline procedure call> ::= [ <variable scope clause> ] <nested procedure specification>

<variable scope clause> ::= "(" [ <binding variable reference list> ] ")"

<binding variable reference list> ::= 
  <binding variable reference> [ { "," <binding variable reference> }... ]

<binding variable reference> ::= <binding variable>
  
<nested procedure specification> ::= "{" <procedure specification> "}"

<procedure specification> ::=
    <catalog-modifying procedure specification>
  | <data-modifying procedure specification>
  | <query specification>

<catalog-modifying procedure specification> ::= <procedure body>

<nested data-modifying procedure specification> ::=
  "{" <data-modifying procedure specification> "}"

<data-modifying procedure specification> ::= <procedure body>

<nested query specification> ::= "{" <query specification> "}"

<query specification> ::= <procedure body>
  
<procedure body> ::= <statement block>

<statement block> ::= <statement> [ <next statement>... ]

<statement> ::=
    <linear catalog-modifying statement>
  | <linear data-modifying statement>
  | <composite query statement>

<next statement> ::=
  "NEXT" [ <yield clause> ] <statement>

The inline procedure call can be constructed with or without importing variables from earlier parts of the query using the variable scope clause.

When there are variables imported, the inline procedure is effectively a subquery, which executes multiple times, each time operating on a single record of the imported variables.

A subquery typically concludes with a RETURN clause. Each row produced by one execution is appended to the right side of the corresponding input row. When an execution yields no output rows, the associated input row is discarded. Conversely, if an execution produces multiple output rows, the corresponding input row is duplicated for each output row, allowing the results to be concatenated accordingly.

Subquery without RETURN are used for data modification operations such as INSERT , SET, REMOVE and DELETE. Since these subqueries do not explicitly return any results, the number of rows present after the subquery remains the same as before the subquery.

When there are no variables imported, the inline procedure only executes one time.

Isolated Querying

In this query, the CALL subquery is used to find the club joined by each user:

MATCH (u:User)
CALL (u) {
    MATCH (u)-[:Joins]-(c:Club)
    RETURN c
}
RETURN u.name, c.code

Result:

The CALL subqeuries enhance efficiency by better managing resources, particularly when working with large datasets, thus reducing memory overhead.

Isolated Aggregation

In this query, the CALL subquery is used to count the total number of followers for each user who joins a club:

MATCH (u:User)-[:Joins]-(c:Club)
CALL (u) {
    MATCH (u)<-[:Follows]-(follower)
    RETURN COUNT(follower) AS followersNo
}
RETURN u.name, c.code, followersNo

Result:

Isolated Data Modification

In this query, the CALL subquery is used to set values for the property rates of edges labeled Joins:

MATCH (u:User)-[j:Joins]-(c:Club)
CALL (j) {
    SET j.rates = ROUND(RAND()*10, 2)
}
RETURN u.name, j.rates, c.code

Result:

Importing Multiple Variables

In this query, the CALL subquery is used to detect the reverse following relationship between two users, where it is known that one follows the other:

MATCH (u1:User)<-[:Follows]-(u2:User)
CALL (u1, u2) {
    OPTIONAL MATCH p = (u1)-[:Follows]->(u2)
    RETURN p
}
RETURN u1.name, u2.name, 
       CASE p WHEN IS NOT NULL THEN "Y"
       ELSE "N" END AS MutualFollowing

Result:

Importing No Variables

In this query, the CALL subquery is used to import no variables and it is executed two times:

MATCH (c:Club)
CALL {
    MATCH (u:User)
    RETURN COUNT(u) AS totalUsers
}
RETURN COUNT(c) AS totalClubs, totalUsers

Result:

Multiple CALL Subqueries

In this query, the first CALL subquery is used to set values for the property rates of edges labeled Joins, the second CALL subquery is used to count the average rating that each club receives:

MATCH (u:User)-[j1:Joins]-(c:Club)
CALL (j1) {
    SET j1.rates = ROUND(RAND()*10, 2)
}
CALL (c) 
    MATCH (c)-[j2]-()
    RETURN ROUND(AVG(j2.rates), 2) as rating
}
RETURN u.name, j1.rates, c.code, rating

Result:

Setting the Order of Subqueries

The order in which the subquery executed is not predetermined. If a specific execution order is desried, the ORDER BY clause should be used before the CALL subquery to enforce that sequence.

In this query, the CALL subquery is used to count the total number of followers for each user; the exection order of the subqueries is determined by the ascending order of the users' names:

MATCH (u:User)
ORDER BY u.name
CALL (u) {
    MATCH (u)<-[:Follows]-(follower)
    RETURN COUNT(follower) AS followersNo
}
RETURN u.name, followersNo

Result: