jihot2000 opened a new issue, #220:
URL: https://github.com/apache/incubator-age/issues/220
**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**
```sql
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
