Question 89921 in PostgreSQL Question posted in
The official documentation can be found here.

Apache AGE – Creating Materialized View Error – Postgresql

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
Tags:

2

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    Unfortunately, PostgreSQL doesn’t support creating materialized views directly from the Cypher queries in this manner.

    As Humza mentioned, you can achieve a similar result by creating a temporary table and inserting the data from the Cypher query into the table. Then, you can create indexes on the columns of that table. Here’s how you can do it:

    1- Create a table to store the data:

    CREATE TABLE book_view (
    author text,
    title text
);

    2- Insert the data from the Cypher query into the table:

    INSERT INTO book_view (author, title)
SELECT
    vertex->'properties'->>'author' as author,
    vertex->'properties'->>'title' as title
FROM cypher('demo', $$
    MATCH (v:Book)
    RETURN v
$$) as (vertex agtype);

    3- Create indexes on the columns of the table:

    CREATE INDEX book_view_author_idx ON book_view (author);
CREATE INDEX book_view_title_idx ON book_view (title);

    Now, you have a table called book_view that contains the data from your Cypher query, and indexes have been created on the author and title columns. You can query this table like any other PostgreSQL table.

    IMPORTANT!! Keep in mind that the table will not be automatically updated when the graph data changes. You will need to update the table manually or create triggers to keep it in sync with the graph data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search