age: VLE still has a bug when I try to run PL/pgSQL functions with VLE statements inside
Describe the bug
VLE still has a bug when I try to run my PL/pgSQL functions with VLE statements inside.
How are you accessing AGE (Command line, driver, etc.)? run a sql file using psql command line
the sql file
CREATE SCHEMA test_vle;
SET search_path = test_vle, ag_catalog, "$user";
SELECT create_graph('mygraph');
SELECT create_vlabel('mygraph', 'head');
SELECT create_vlabel('mygraph', 'tail');
SELECT create_vlabel('mygraph', 'node');
SELECT create_elabel('mygraph', 'next');
CREATE OR REPLACE FUNCTION create_list(list_name text)
RETURNS void
LANGUAGE 'plpgsql'
AS $$
DECLARE
ag_param agtype;
BEGIN
ag_param = FORMAT('{"list_name": "%s"}', $1)::agtype;
PERFORM * FROM cypher('mygraph', $CYPHER$
MERGE (:head {name: $list_name})-[:next]->(:tail {name: $list_name})
$CYPHER$, ag_param) AS (a agtype);
END $$;
CREATE OR REPLACE FUNCTION prepend_node(list_name text, node_content text)
RETURNS void
LANGUAGE 'plpgsql'
AS $$
DECLARE
ag_param agtype;
BEGIN
ag_param = FORMAT('{"list_name": "%s", "node_content": "%s"}', $1, $2)::agtype;
PERFORM * FROM cypher('mygraph', $CYPHER$
MATCH (h:head {name: $list_name})-[e:next]->(v)
DELETE e
CREATE (h)-[:next]->(:node {content: $node_content})-[:next]->(v)
$CYPHER$, ag_param) AS (a agtype);
END $$;
CREATE OR REPLACE FUNCTION show_list_use_vle(list_name text)
RETURNS TABLE(node agtype)
LANGUAGE 'plpgsql'
AS $$
DECLARE
ag_param agtype;
BEGIN
ag_param = FORMAT('{"list_name": "%s"}', $1)::agtype;
RETURN QUERY
SELECT * FROM cypher('mygraph', $CYPHER$
MATCH (h:head {name: $list_name})-[e:next*]->(v:node)
RETURN v
$CYPHER$, ag_param) AS (node agtype);
END $$;
CREATE OR REPLACE FUNCTION show_list_use_loop(list_name text)
RETURNS TABLE(node agtype)
LANGUAGE 'plpgsql'
AS $$
DECLARE
node_id bigint;
ag_param agtype;
BEGIN
ag_param = FORMAT('{"list_name": "%s"}', $1)::agtype;
SELECT g.id INTO node_id FROM cypher('mygraph', $CYPHER$
MATCH (h:head {name: $list_name})
RETURN id(h)
$CYPHER$, ag_param) AS g(id bigint);
IF node_id IS NULL THEN
RETURN;
END IF;
LOOP
ag_param = FORMAT('{"node_id": %s}', node_id)::agtype;
SELECT g.id, g.node INTO node_id, node FROM cypher('mygraph', $CYPHER$
MATCH (p)-[e:next]->(v:node)
WHERE id(p) = $node_id
RETURN id(v), v
$CYPHER$, ag_param) AS g(id bigint, node agtype);
EXIT WHEN node_id IS NULL;
RETURN NEXT;
END LOOP;
END $$;
-- create a list
SELECT create_list('list01');
-- prepend a node 'a'
SELECT prepend_node('list01', 'a');
SELECT * FROM show_list_use_vle('list01'); -- this line shows node 'a' as expected
SELECT * FROM show_list_use_loop('list01'); -- this line show node 'a' as expected
-- prepend a node 'b'
SELECT prepend_node('list01', 'b');
SELECT * FROM show_list_use_vle('list01'); -- bug: this line only shows node 'a'
SELECT * FROM show_list_use_loop('list01'); -- this line shows node 'b' and 'a' as expected
SELECT drop_graph('mygraph', true);
DROP SCHEMA test_vle CASCADE;
Expected behavior
The bug show_list_use_vle line only shows node ‘a’:
node
-----------------------------------------------------------------------------------
{"id": 1407374883553281, "label": "node", "properties": {"content": "a"}}::vertex
(1 row)
The show_list_use_loop line shows the expected result:
node
-----------------------------------------------------------------------------------
{"id": 1407374883553282, "label": "node", "properties": {"content": "b"}}::vertex
{"id": 1407374883553281, "label": "node", "properties": {"content": "a"}}::vertex
(2 rows)
Environment (please complete the following information): PostgreSQL 11 and AGE commit id 95ca659
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 22 (11 by maintainers)
I have created a patch, that is currently in review, that should address this particular issue. It should be ready by the end of the week.