create table as select * from cypher()... throw an error

[clxman]

Describe the bug When execute 'create table as select * from cypher(...)' using pgsql, the sql will return an error, if I create table xxx first, and then insert into xxx select ... the result is ok

What is the command that caused the error?

postgresDB=# create table tt as SELECT * FROM cypher('agload_test_graph', $$MATCH (a)-[e]->(b) RETURN e$$) as (n agtype) limit 1;
ERROR:  unhandled cypher(cstring) function call
DETAIL:  agload_test_graph
postgresDB=# create table tt(n agtype);
CREATE TABLE
postgresDB=# insert into tt SELECT * FROM cypher('agload_test_graph', $$MATCH (a)-[e]->(b) RETURN e$$) as (n agtype) limit 1;
INSERT 0 1

Expected behavior It should ok for create table as select ... I think maybe need a litter change for hook of parser for DDL

Environment (please complete the following information):

• Use apache/age docker to test

[diangamichael]

It seems like you're encountering an error when trying to execute a SQL command that creates a table using the results of a Cypher query in PostgreSQL. The error message indicates that there's an unhandled Cypher function call.

Based on the provided information, it looks like the issue might be related to how PostgreSQL handles the Cypher function call within the CREATE TABLE AS SELECT statement. One workaround you've found is to first create an empty table and then insert the results of the Cypher query into that table, which seems to work fine.

To address this, you may need to modify the parser or add a hook to handle Cypher function calls within the context of CREATE TABLE AS SELECT statements. This could involve updating the PostgreSQL extension you're using to handle Cypher queries or making adjustments to the PostgreSQL configuration to support this functionality.

[jrgemignani]

@diangamichael Please stop with the ChatGPT responses. This isn't helpful to the Apache AGE community.

[jrgemignani]

@clxman Thank you for bringing this to our attention. I will try to take a look to see what the issue might be.

[jrgemignani]

@clxman CREATE is a "utility" command, unlike insert, and doesn't appear to work with the logic for transforming the cypher function node. From what I've seen in the debugger, it doesn't look like this will be possible to implement. Or rather, it isn't immediately obvious how it can be done.

For now, just create the table first and then use insert - your workaround.

I will update you if I find anything else.

edit: we should consider adding this to our documentation

[jrgemignani]

@clxman I have found out how to get this to work and will have a PR to address this for the master branch shortly -

psql-16.1-5432-pgsql=# SELECT * FROM cypher('test', $$ MATCH (a)-[e]->(b) RETURN e $$) as (n agtype);
                                                             n
---------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "knows", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge
 {"id": 844424930131970, "label": "knows", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge
 {"id": 844424930131971, "label": "knows", "end_id": 281474976710662, "start_id": 281474976710661, "properties": {}}::edge
 {"id": 844424930131972, "label": "knows", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {}}::edge
 {"id": 844424930131973, "label": "knows", "end_id": 281474976710666, "start_id": 281474976710665, "properties": {}}::edge
(5 rows)

psql-16.1-5432-pgsql=# create table tt3 as (SELECT * FROM cypher('test', $$ MATCH (a)-[e]->(b) RETURN e $$) as (e agtype));
SELECT 5
psql-16.1-5432-pgsql=# select * from tt3;
                                                             e
---------------------------------------------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "knows", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge
 {"id": 844424930131970, "label": "knows", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge
 {"id": 844424930131971, "label": "knows", "end_id": 281474976710662, "start_id": 281474976710661, "properties": {}}::edge
 {"id": 844424930131972, "label": "knows", "end_id": 281474976710664, "start_id": 281474976710663, "properties": {}}::edge
 {"id": 844424930131973, "label": "knows", "end_id": 281474976710666, "start_id": 281474976710665, "properties": {}}::edge
(5 rows)

psql-16.1-5432-pgsql=#

[jrgemignani]

@clxman I have created PR #1799 to correct this issue.

[jrgemignani]

@clxman The PR has been merged and all branches have been update. Please verify that this resolves your issue. And, if it does, please close the ticket :)