0

I wanted to create a materialized view from a label table and then create indexes on it. However, when I type the query to create this view, postgres pops an error.

Here is the query that I type to return all the vertices containing the "Book" label :

demo=# SELECT * FROM cypher ('demo', $$
demo$# MATCH (v:Book)
demo$# RETURN v
demo$# $$) as (vertex agtype);
                                                                vertex
---------------------------------------------------------------------------------------------------------------------------------------
 {"id": 1125899906842625, "label": "Book", "properties": {"title": "The Hobbit"}}::vertex
 {"id": 1125899906842626, "label": "Book", "properties": {"title": "SPQR: A History of Ancient Rome", "author": "Mary Beard"}}::vertex
(2 rows)

Here is the way that I'm creating the materialized view :

demo=# CREATE MATERIALIZED VIEW book_view AS SELECT * FROM cypher ('demo', $$
MATCH (v:Book)
RETURN v.author, v.title
$$) as (author agtype, title agtype);

ERROR:  unhandled cypher(cstring) function call
DETAIL:  demo
Matheus Farias
  • 716
  • 1
  • 10

2 Answers2

1

I believe you can't call a function like cypher to create a view. Therefore, you should try using a SQL SELECT statement like this:

CREATE MATERIALIZED VIEW book_view AS 
    SELECT properties->'author' AS author,
           properties->'title' AS title 
    FROM demo."Book";
Wendel
  • 763
  • 1
  • 12
0

You can try a workaround by first creating a temporary table and populating it with the results of your Cypher query. Then, create a materialized view using the data from the temporary table. Use the following steps:

  • Create a temporary table to store the results of your Cypher query
  • Create the materialized view using the data from the temporary table
  • Optionally, create indexes on the materialized view