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)

Commits related to this issue

Most upvoted comments

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.