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.