src/test/regress/expected/graph_table.out - master - githubmirror/postgres

src/test/regress/expected/graph_table.out
1 130 строк49 KB
Peter Eisentraut
Disallow aggregates, window functions, and SRFs in GRAPH_TABLE COLUMNS
05 авг 2026, 11:52
05 авг 2026, 11:52f585671
CREATE SCHEMA graph_table_tests;
GRANT USAGE ON SCHEMA graph_table_tests TO PUBLIC;
SET search_path = graph_table_tests;
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name varchar,
    price numeric
);
CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    name varchar,
    address varchar
);
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    ordered_when date
);
CREATE TABLE order_items (
    order_items_id integer PRIMARY KEY,
    order_id integer REFERENCES orders (order_id),
    product_no integer REFERENCES products (product_no),
    quantity integer
);
CREATE TABLE customer_orders (
    customer_orders_id integer PRIMARY KEY,
    customer_id integer REFERENCES customers (customer_id),
    order_id integer REFERENCES orders (order_id)
);
CREATE TABLE wishlists (
    wishlist_id integer PRIMARY KEY,
    wishlist_name varchar
);
CREATE TABLE wishlist_items (
    wishlist_items_id integer PRIMARY KEY,
    wishlist_id integer REFERENCES wishlists (wishlist_id),
    product_no integer REFERENCES products (product_no)
);
CREATE TABLE customer_wishlists (
    customer_wishlist_id integer PRIMARY KEY,
    customer_id integer REFERENCES customers (customer_id),
    wishlist_id integer REFERENCES wishlists (wishlist_id)
);
CREATE PROPERTY GRAPH myshop
    VERTEX TABLES (
        products,
        customers,
        orders
           DEFAULT LABEL
           LABEL lists PROPERTIES (order_id AS node_id, 'order'::varchar(10) AS list_type),
        wishlists
           DEFAULT LABEL
           LABEL lists PROPERTIES (wishlist_id AS node_id, 'wishlist'::varchar(10) AS list_type)
    )
    EDGE TABLES (
        order_items KEY (order_items_id)
            SOURCE KEY (order_id) REFERENCES orders (order_id)
            DESTINATION KEY (product_no) REFERENCES products (product_no)
            DEFAULT LABEL
            LABEL list_items PROPERTIES (order_id AS link_id, product_no),
        wishlist_items KEY (wishlist_items_id)
            SOURCE KEY (wishlist_id) REFERENCES wishlists (wishlist_id)
            DESTINATION KEY (product_no) REFERENCES products (product_no)
            DEFAULT LABEL
            LABEL list_items PROPERTIES (wishlist_id AS link_id, product_no),
        customer_orders KEY (customer_orders_id)
            SOURCE KEY (customer_id) REFERENCES customers (customer_id)
            DESTINATION KEY (order_id) REFERENCES orders (order_id)
            DEFAULT LABEL
            LABEL cust_lists PROPERTIES (customer_id, order_id AS link_id),
        customer_wishlists KEY (customer_wishlist_id)
            SOURCE KEY (customer_id) REFERENCES customers (customer_id)
            DESTINATION KEY (wishlist_id) REFERENCES wishlists (wishlist_id)
            DEFAULT LABEL
            LABEL cust_lists PROPERTIES (customer_id, wishlist_id AS link_id)
    );
SELECT customer_name FROM GRAPH_TABLE (xxx MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));  -- error
ERROR:  relation "xxx" does not exist
LINE 1: SELECT customer_name FROM GRAPH_TABLE (xxx MATCH (c IS custo...
                                               ^
SELECT customer_name FROM GRAPH_TABLE (pg_class MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));  -- error
ERROR:  "pg_class" is not a property graph
LINE 1: SELECT customer_name FROM GRAPH_TABLE (pg_class MATCH (c IS ...
                                               ^
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (cx.name AS customer_name));  -- error
ERROR:  missing FROM-clause entry for table "cx"
LINE 1: ...US')-[IS customer_orders]->(o IS orders) COLUMNS (cx.name AS...
                                                             ^
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.namex AS customer_name));  -- error
ERROR:  property "namex" does not exist
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers|employees WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name));  -- error
ERROR:  label "employees" does not exist in property graph "myshop"
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders] COLUMNS (c.name AS customer_name));  -- error
ERROR:  syntax error at or near "COLUMNS"
LINE 1: ...mers WHERE c.address = 'US')-[IS customer_orders] COLUMNS (c...
                                                             ^
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers), (o IS orders) COLUMNS (c.name AS customer_name));  -- error
ERROR:  multiple path patterns in one GRAPH_TABLE clause not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col));  -- error, empty match clause
ERROR:  syntax error at or near "COLUMNS"
LINE 1: SELECT * FROM GRAPH_TABLE (myshop MATCH COLUMNS (1 AS col));
                                                ^
SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)->{1,2}(o IS orders) COLUMNS (c.name AS customer_name));  -- error
ERROR:  element pattern quantifier is not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH ((c IS customers)->(o IS orders)) COLUMNS (c.name));
ERROR:  unsupported element pattern kind: "nested path pattern"
LINE 1: SELECT * FROM GRAPH_TABLE (myshop MATCH ((c IS customers)->(...
                                                ^
-- a property graph can be referenced only from within GRAPH_TABLE clause.
SELECT * FROM myshop; -- error
ERROR:  cannot open relation "myshop"
LINE 1: SELECT * FROM myshop;
                      ^
DETAIL:  This operation is not supported for property graphs.
COPY myshop TO stdout; -- error
ERROR:  cannot open relation "myshop"
DETAIL:  This operation is not supported for property graphs.
INSERT INTO myshop VALUES (1); -- error
ERROR:  cannot open relation "myshop"
LINE 1: INSERT INTO myshop VALUES (1);
                    ^
DETAIL:  This operation is not supported for property graphs.
INSERT INTO products VALUES
    (1, 'product1', 10),
    (2, 'product2', 20),
    (3, 'product3', 30);
INSERT INTO customers VALUES
    (1, 'customer1', 'US'),
    (2, 'customer2', 'CA'),
    (3, 'customer3', 'GL');
INSERT INTO orders VALUES
    (1, date '2024-01-01'),
    (2, date '2024-01-02'),
    (3, date '2024-01-03');
INSERT INTO wishlists VALUES
    (1, 'wishlist1'),
    (2, 'wishlist2'),
    (3, 'wishlist3');
INSERT INTO order_items (order_items_id, order_id, product_no, quantity) VALUES
    (1, 1, 1, 5),
    (2, 1, 2, 10),
    (3, 2, 1, 7);
INSERT INTO customer_orders (customer_orders_id, customer_id, order_id) VALUES
    (1, 1, 1),
    (2, 2, 2);
INSERT INTO customer_wishlists (customer_wishlist_id, customer_id, wishlist_id) VALUES
    (1, 2, 3),
    (2, 3, 1),
    (3, 3, 2);
INSERT INTO wishlist_items (wishlist_items_id, wishlist_id, product_no) VALUES
    (1, 1, 2),
    (2, 1, 3),
    (3, 2, 1),
    (4, 3, 1);
-- single element path pattern
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (c.name));
   name    
-----------
 customer1
 customer2
 customer3
(3 rows)

-- unknown type resolution
SELECT *, pg_typeof(unknown_col) AS unknown_col_type, pg_typeof(null_col) AS null_col_type FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (c.name, 'unknown-literal' AS unknown_col, NULL AS null_col));
   name    |   unknown_col   | null_col | unknown_col_type | null_col_type 
-----------+-----------------+----------+------------------+---------------
 customer1 | unknown-literal |          | text             | text
 customer2 | unknown-literal |          | text             | text
 customer3 | unknown-literal |          | text             | text
(3 rows)

SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name));
   name    
-----------
 customer1
(1 row)

-- graph element specification without label or variable
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[]->(o IS orders) COLUMNS (c.name AS customer_name));
 customer_name 
---------------
 customer1
(1 row)

SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[co IS customer_orders]->(o IS orders WHERE o.ordered_when = date '2024-01-02') COLUMNS (c.name, c.address));
   name    | address 
-----------+---------
 customer2 | CA
(1 row)

SELECT * FROM GRAPH_TABLE (myshop MATCH (o IS orders)-[IS customer_orders]->(c IS customers) COLUMNS (c.name, o.ordered_when));
 name | ordered_when 
------+--------------
(0 rows)

SELECT * FROM GRAPH_TABLE (myshop MATCH (o IS orders)<-[IS customer_orders]-(c IS customers) COLUMNS (c.name, o.ordered_when));
   name    | ordered_when 
-----------+--------------
 customer1 | 01-01-2024
 customer2 | 01-02-2024
(2 rows)

-- spaces around pattern operators
SELECT * FROM GRAPH_TABLE (myshop MATCH ( o IS orders ) <- [ IS customer_orders ] - (c IS customers) COLUMNS ( c.name, o.ordered_when));
   name    | ordered_when 
-----------+--------------
 customer1 | 01-01-2024
 customer2 | 01-02-2024
(2 rows)

SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS cust_lists]->(l IS lists)-[ IS list_items]->(p IS products) COLUMNS (c.name AS customer_name, p.name AS product_name, l.list_type)) ORDER BY customer_name, product_name, list_type;
 customer_name | product_name | list_type 
---------------+--------------+-----------
 customer1     | product1     | order
 customer1     | product2     | order
 customer2     | product1     | order
 customer2     | product1     | wishlist
 customer3     | product1     | wishlist
 customer3     | product2     | wishlist
 customer3     | product3     | wishlist
(7 rows)

-- label disjunction
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders | customer_wishlists ]->(l IS orders | wishlists)-[ IS list_items]->(p IS products) COLUMNS (c.name AS customer_name, p.name AS product_name)) ORDER BY customer_name, product_name;
 customer_name | product_name 
---------------+--------------
 customer1     | product1
 customer1     | product2
 customer2     | product1
 customer2     | product1
 customer3     | product1
 customer3     | product2
 customer3     | product3
(7 rows)

-- property not associated with labels queried results in error
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders | customer_wishlists ]->(l IS orders | wishlists)-[ IS list_items]->(p IS products) COLUMNS (c.name AS customer_name, p.name AS product_name, l.list_type)) ORDER BY 1, 2, 3;
ERROR:  property "list_type" for element variable "l" not found
-- vertex to vertex connection abbreviation
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers)->(o IS orders) COLUMNS (c.name, o.ordered_when)) ORDER BY 1;
   name    | ordered_when 
-----------+--------------
 customer1 | 01-01-2024
 customer2 | 01-02-2024
(2 rows)

-- lateral test
-- Use table with a column name same as a property in the property graph so as
-- to test resolution preferences. Property references are preferred over
-- lateral table references.
CREATE TABLE x1 (a int, address text, flag boolean);
INSERT INTO x1 VALUES (1, 'one', true), (2, 'two', false);
SELECT * FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US' AND c.customer_id = x1.a)-[IS customer_orders]->(o IS orders) COLUMNS (c.name AS customer_name, c.customer_id AS cid));
 a | address | flag | customer_name | cid 
---+---------+------+---------------+-----
 1 | one     | t    | customer1     |   1
(1 row)

SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (x1 IS customers WHERE x1.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (x1.name AS customer_name, x1.customer_id AS cid, o.order_id)) g;
 a | customer_name | cid | order_id 
---+---------------+-----+----------
 1 | customer1     |   1 |        1
 2 | customer1     |   1 |        1
(2 rows)

-- bare lateral reference in WHERE clause
SELECT * FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.customer_id = x1.a) WHERE x1.flag COLUMNS (c.name AS customer_name));
 a | address | flag | customer_name 
---+---------+------+---------------
 1 | one     | t    | customer1
(1 row)

-- lateral reference with multi-label pattern, which is rewritten as UNION of
-- path queries
SELECT x1.a, g.* FROM x1,
    GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.customer_id = x1.a)-[IS customer_orders | customer_wishlists]->(l IS lists)-[IS list_items]->(p IS products)
        COLUMNS (x1.a AS outer_id, c.name AS customer_name, p.name AS product_name, l.list_type)) g
    ORDER BY 1, 3, 4, 5;
 a | outer_id | customer_name | product_name | list_type 
---+----------+---------------+--------------+-----------
 1 |        1 | customer1     | product1     | order
 1 |        1 | customer1     | product2     | order
 2 |        2 | customer2     | product1     | order
 2 |        2 | customer2     | product1     | wishlist
(4 rows)

-- non-local property references are not allowed, even if a lateral column
-- reference is available
SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (x1 IS customers)-[IS customer_orders]->(o IS orders WHERE o.order_id = x1.a) COLUMNS (x1.name AS customer_name, x1.customer_id AS cid, o.order_id)) g; -- error
ERROR:  non-local element variable reference is not supported
LINE 1: ...customer_orders]->(o IS orders WHERE o.order_id = x1.a) COLU...
                                                             ^
SELECT x1.a, g.* FROM x1, GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US' AND c.customer_id = x1.a)-[IS customer_orders]->(x1 IS orders) COLUMNS (c.name AS customer_name, c.customer_id AS cid, x1.order_id)) g; -- error
ERROR:  non-local element variable reference is not supported
LINE 1: ...tomers WHERE c.address = 'US' AND c.customer_id = x1.a)-[IS ...
                                                             ^
CREATE TABLE v1 (
    id int PRIMARY KEY,
    vname varchar(10),
    vprop1 int,
    vprop2 int
);
CREATE TABLE v2 (
    id1 int,
    id2 int,
    vname varchar(10),
    vprop1 int,
    vprop2 int
);
CREATE TABLE v3 (
    id int PRIMARY KEY,
    vname varchar(10),
    vprop1 int,
    vprop2 int
);
-- edge connecting v1 and v2
CREATE TABLE e1_2 (
    id_1 int,
    id_2_1 int,
    id_2_2 int,
    ename varchar(10),
    eprop1 int
);
-- edge connecting v1 and v3
CREATE TABLE e1_3 (
    id_1 int,
    id_3 int,
    ename varchar(10),
    eprop1 int,
    PRIMARY KEY (id_1, id_3)
);
CREATE TABLE e2_3 (
    id_2_1 int,
    id_2_2 int,
    id_3 int,
    ename varchar(10),
    eprop1 int
);
CREATE PROPERTY GRAPH g1
    VERTEX TABLES (
        v1
            LABEL vl1 PROPERTIES (vname, vprop1)
            LABEL l1 PROPERTIES (vname AS elname), -- label shared by vertices as well as edges
        v2 KEY (id1, id2)
            LABEL vl2 PROPERTIES (vname, vprop2, 'vl2_prop'::varchar(10) AS lprop1)
            LABEL vl3 PROPERTIES (vname, vprop1, 'vl2_prop'::varchar(10) AS lprop1)
            LABEL l1 PROPERTIES (vname AS elname),
        v3
            LABEL vl3 PROPERTIES (vname, vprop1, 'vl3_prop'::varchar(10) AS lprop1)
            LABEL l1 PROPERTIES (vname AS elname)
    )
    -- edges with differing number of columns in destination keys
    EDGE TABLES (
        e1_2 key (id_1, id_2_1, id_2_2)
            SOURCE KEY (id_1) REFERENCES v1 (id)
            DESTINATION KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            LABEL el1 PROPERTIES (eprop1, ename)
            LABEL l1 PROPERTIES (ename AS elname),
        e1_3
            SOURCE KEY (id_1) REFERENCES v1 (id)
            DESTINATION KEY (id_3) REFERENCES v3 (id)
            -- order of property names doesn't matter
            LABEL el1 PROPERTIES (ename, eprop1)
            LABEL l1 PROPERTIES (ename AS elname),
        e2_3 key (id_2_1, id_2_2, id_3)
            SOURCE KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            DESTINATION KEY (id_3) REFERENCES v3 (id)
            -- new property lprop2 not shared by el1
            -- does not share eprop1 from by el1
            LABEL el2 PROPERTIES (ename, eprop1 * 10 AS lprop2)
            LABEL l1 PROPERTIES (ename AS elname)
    );
INSERT INTO v1 VALUES
    (1, 'v11', 10, 100),
    (2, 'v12', 20, 200),
    (3, 'v13', 30, 300);
INSERT INTO v2 VALUES
    (1000, 1, 'v21', 1010, 1100),
    (1000, 2, 'v22', 1020, 1200),
    (1000, 3, 'v23', 1030, 1300);
INSERT INTO v3 VALUES
    (2001, 'v31', 2010, 2100),
    (2002, 'v32', 2020, 2200),
    (2003, 'v33', 2030, 2300);
INSERT INTO e1_2 VALUES
    (1, 1000, 2, 'e121', 10001),
    (2, 1000, 1, 'e122', 10002);
INSERT INTO e1_3 VALUES
    (1, 2003, 'e131', 10003),
    (1, 2001, 'e132', 10004);
INSERT INTO e2_3 VALUES (1000, 2, 2002, 'e231', 10005);
-- empty element path pattern, counts number of edges in the graph
SELECT count(*) FROM GRAPH_TABLE (g1 MATCH ()-[]->() COLUMNS (1 AS one));
 count 
-------
     5
(1 row)

SELECT count(*) FROM GRAPH_TABLE (g1 MATCH ()->() COLUMNS (1 AS one));
 count 
-------
     5
(1 row)

-- Project property associated with a label specified in the graph pattern even
-- if it is defined for a graph element through a different label. (Refer
-- section 6.5 of SQL/PGQ standard). For example, vprop1 in the query below. It
-- is defined on v2 through label vl3, but gets exposed in the query through
-- label vl1 which is not associated with v2. v2, in turn, is included because
-- of label vl2.
SELECT * FROM GRAPH_TABLE (g1 MATCH (a IS vl1 | vl2) COLUMNS (a.vname, a.vprop1));
 vname | vprop1 
-------+--------
 v11   |     10
 v12   |     20
 v13   |     30
 v21   |   1010
 v22   |   1020
 v23   |   1030
(6 rows)

-- vprop2 is associated with vl2 but not vl3
SELECT src, conn, dest, lprop1, vprop2, vprop1 FROM GRAPH_TABLE (g1 MATCH (a IS vl1)-[b IS el1]->(c IS vl2 | vl3) COLUMNS (a.vname AS src, b.ename AS conn, c.vname AS dest, c.lprop1, c.vprop2, c.vprop1));
 src | conn | dest |  lprop1  | vprop2 | vprop1 
-----+------+------+----------+--------+--------
 v12 | e122 | v21  | vl2_prop |   1100 |   1010
 v11 | e121 | v22  | vl2_prop |   1200 |   1020
 v11 | e131 | v33  | vl3_prop |        |   2030
 v11 | e132 | v31  | vl3_prop |        |   2010
(4 rows)

-- edges directed in both ways - to and from v2
SELECT * FROM GRAPH_TABLE (g1 MATCH (v1 IS vl2)-[conn]-(v2) COLUMNS (v1.vname AS v1name, conn.ename AS cname, v2.vname AS v2name));
 v1name | cname | v2name 
--------+-------+--------
 v21    | e122  | v12
 v22    | e121  | v11
 v22    | e231  | v32
(3 rows)

SELECT * FROM GRAPH_TABLE (g1 MATCH (v1 IS vl2)-(v2) COLUMNS (v1.vname AS v1name, v2.vname AS v2name));
 v1name | v2name 
--------+--------
 v21    | v12
 v22    | v11
 v22    | v32
(3 rows)

-- Errors
-- vl1 is not associated with property vprop2
SELECT src, src_vprop2, conn, dest FROM GRAPH_TABLE (g1 MATCH (a IS vl1)-[b IS el1]->(c IS vl2 | vl3) COLUMNS (a.vname AS src, a.vprop2 AS src_vprop2, b.ename AS conn, c.vname AS dest));
ERROR:  property "vprop2" for element variable "a" not found
-- property ename is associated with edge labels but not with a vertex label
SELECT * FROM GRAPH_TABLE (g1 MATCH (src)-[conn]->(dest) COLUMNS (src.vname AS svname, src.ename AS sename));
ERROR:  property "ename" for element variable "src" not found
-- vname is associated vertex labels but not with an edge label
SELECT * FROM GRAPH_TABLE (g1 MATCH (src)-[conn]->(dest) COLUMNS (conn.vname AS cvname, conn.ename AS cename));
ERROR:  property "vname" for element variable "conn" not found
-- el1 is associated with only edges, and cannot qualify a vertex
SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS el1)-[conn]->(dest) COLUMNS (conn.ename AS cename));
ERROR:  no property graph element of type "vertex" has label "el1" associated with it in property graph "g1"
SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS el1 | vl1)-[conn]->(dest) COLUMNS (conn.ename AS cename));
ERROR:  no property graph element of type "vertex" has label "el1" associated with it in property graph "g1"
-- star in COLUMNs is specified but not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.*));
ERROR:  "*" is not supported here
LINE 1: ... = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.*));
                                                                 ^
-- star anywhere else is not allowed as a property reference
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.* IS NOT NULL)-[IS customer_orders]->(o IS orders) COLUMNS (c.name));
ERROR:  "*" not allowed here
LINE 1: ...M GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.* IS NOT...
                                                             ^
-- aggregate, window, and set-returning functions are not supported in COLUMNS
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (count(*) AS num));
ERROR:  aggregate functions in GRAPH_TABLE COLUMNS are not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (row_number() OVER () AS rn));
ERROR:  window functions in GRAPH_TABLE COLUMNS are not supported
SELECT * FROM GRAPH_TABLE (myshop MATCH (c IS customers) COLUMNS (generate_series(1, 2) AS gs));
ERROR:  set-returning functions in GRAPH_TABLE COLUMNS are not supported
-- consecutive element patterns with same kind
SELECT * FROM GRAPH_TABLE (g1 MATCH ()() COLUMNS (1 as one));
ERROR:  adjacent vertex patterns are not supported
LINE 1: SELECT * FROM GRAPH_TABLE (g1 MATCH ()() COLUMNS (1 as one))...
                                              ^
SELECT * FROM GRAPH_TABLE (g1 MATCH -> COLUMNS (1 AS one));
ERROR:  path pattern cannot start with an edge pattern
LINE 1: SELECT * FROM GRAPH_TABLE (g1 MATCH -> COLUMNS (1 AS one));
                                            ^
SELECT * FROM GRAPH_TABLE (g1 MATCH ()-[]- COLUMNS (1 AS one));
ERROR:  path pattern cannot end with an edge pattern
LINE 1: SELECT * FROM GRAPH_TABLE (g1 MATCH ()-[]- COLUMNS (1 AS one...
                                              ^
SELECT * FROM GRAPH_TABLE (g1 MATCH ()-> ->() COLUMNS (1 AS one));
ERROR:  edge pattern must be preceded by a vertex pattern
LINE 1: SELECT * FROM GRAPH_TABLE (g1 MATCH ()-> ->() COLUMNS (1 AS ...
                                                 ^
-- non-local element variable reference with element patterns without variable
-- names
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[WHERE a.vprop1 = 10]->(c) COLUMNS (a.vname AS aname, c.vname AS cname));
ERROR:  non-local element variable reference is not supported
LINE 1: SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[WHERE a.vprop1 = 10...
                                                       ^
SELECT * FROM GRAPH_TABLE (g1 MATCH (WHERE b.eprop1 = 10001)-[b]->(c) COLUMNS (b.ename AS bname, c.vname AS cname));
ERROR:  non-local element variable reference is not supported
LINE 1: SELECT * FROM GRAPH_TABLE (g1 MATCH (WHERE b.eprop1 = 10001)...
                                                   ^
-- select all the properties across all the labels associated with a given type
-- of graph element
SELECT * FROM GRAPH_TABLE (g1 MATCH (src)-[conn]->(dest) COLUMNS (src.vname AS svname, conn.ename AS cename, dest.vname AS dvname, src.vprop1 AS svp1, src.vprop2 AS svp2, src.lprop1 AS slp1, dest.vprop1 AS dvp1, dest.vprop2 AS dvp2, dest.lprop1 AS dlp1, conn.eprop1 AS cep1, conn.lprop2 AS clp2));
 svname | cename | dvname | svp1 | svp2 |   slp1   | dvp1 | dvp2 |   dlp1   | cep1  |  clp2  
--------+--------+--------+------+------+----------+------+------+----------+-------+--------
 v12    | e122   | v21    |   20 |      |          | 1010 | 1100 | vl2_prop | 10002 |       
 v11    | e121   | v22    |   10 |      |          | 1020 | 1200 | vl2_prop | 10001 |       
 v11    | e131   | v33    |   10 |      |          | 2030 |      | vl3_prop | 10003 |       
 v11    | e132   | v31    |   10 |      |          | 2010 |      | vl3_prop | 10004 |       
 v22    | e231   | v32    | 1020 | 1200 | vl2_prop | 2020 |      | vl3_prop |       | 100050
(5 rows)

-- three label disjunction
SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl1 | vl2 | vl3)-[conn]->(dest) COLUMNS (src.vname AS svname, conn.ename AS cename, dest.vname AS dvname));
 svname | cename | dvname 
--------+--------+--------
 v12    | e122   | v21
 v11    | e121   | v22
 v11    | e131   | v33
 v11    | e132   | v31
 v22    | e231   | v32
(5 rows)

-- graph'ical query: find a vertex which is not connected to any other vertex as a source or a destination.
WITH all_connected_vertices AS (SELECT svn, dvn FROM GRAPH_TABLE (g1 MATCH (src)-[conn]->(dest) COLUMNS (src.vname AS svn, dest.vname AS dvn))),
    all_vertices AS (SELECT vn FROM GRAPH_TABLE (g1 MATCH (vertex) COLUMNS (vertex.vname AS vn)))
SELECT vn FROM all_vertices EXCEPT (SELECT svn FROM all_connected_vertices UNION SELECT dvn FROM all_connected_vertices) ORDER BY vn;
 vn  
-----
 v13
 v23
(2 rows)

-- query all connections using a label shared by vertices and edges
SELECT sn, cn, dn FROM GRAPH_TABLE (g1 MATCH (src IS l1)-[conn IS l1]->(dest IS l1) COLUMNS (src.elname AS sn, conn.elname AS cn, dest.elname AS dn));
 sn  |  cn  | dn  
-----+------+-----
 v12 | e122 | v21
 v11 | e121 | v22
 v11 | e131 | v33
 v11 | e132 | v31
 v22 | e231 | v32
(5 rows)

-- Tests for cyclic path patterns
CREATE TABLE e2_1 (
    id_2_1 int,
    id_2_2 int,
    id_1 int,
    ename varchar(10),
    eprop1 int
);
CREATE TABLE e3_2 (
    id_3 int,
    id_2_1 int,
    id_2_2 int,
    ename varchar(10),
    eprop1 int
);
ALTER PROPERTY GRAPH g1
    ADD EDGE TABLES (
        e2_1 KEY (id_2_1, id_2_2, id_1)
            SOURCE KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            DESTINATION KEY (id_1) REFERENCES v1 (id)
            LABEL el1 PROPERTIES (eprop1, ename)
            LABEL l1 PROPERTIES (ename AS elname),
        e3_2 KEY (id_3, id_2_1, id_2_2)
            SOURCE KEY (id_3) REFERENCES v3 (id)
            DESTINATION KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            LABEL el2 PROPERTIES (ename, eprop1 * 10 AS lprop2)
            LABEL l1 PROPERTIES (ename AS elname)
    );
INSERT INTO e1_2 VALUES (3, 1000, 3, 'e123', 10007);
INSERT INTO e2_1 VALUES (1000, 1, 2, 'e211', 10006);
INSERT INTO e2_1 VALUES (1000, 3, 3, 'e212', 10008);
INSERT INTO e3_2 VALUES (2002, 1000, 2, 'e321', 10009);
-- cyclic pattern using WHERE clause in graph pattern,
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(c) WHERE a.vname = c.vname COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v12  | v21     |      20 |       1010
 v13  | v23     |      30 |       1030
 v21  | v12     |    1010 |         20
 v22  | v32     |    1020 |       2020
 v23  | v13     |    1030 |         30
 v32  | v22     |    2020 |       1020
(6 rows)

-- cyclic pattern using element patterns with the same variable name
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(a) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v12  | v21     |      20 |       1010
 v13  | v23     |      30 |       1030
 v21  | v12     |    1010 |         20
 v22  | v32     |    1020 |       2020
 v23  | v13     |    1030 |         30
 v32  | v22     |    2020 |       1020
(6 rows)

-- cyclic pattern with WHERE clauses in element patterns with the same variable name
SELECT * FROM GRAPH_TABLE (g1 MATCH (a WHERE a.vprop1 < 2000)->(b WHERE b.vprop1 > 20)->(a WHERE a.vprop1 > 20) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v13  | v23     |      30 |       1030
 v22  | v32     |    1020 |       2020
 v23  | v13     |    1030 |         30
(3 rows)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b WHERE b.vprop1 > 20)->(a WHERE a.vprop1 between 20 and 2000) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v12  | v21     |      20 |       1010
 v13  | v23     |      30 |       1030
 v22  | v32     |    1020 |       2020
 v23  | v13     |    1030 |         30
(4 rows)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a WHERE a.vprop1 between 20 and 2000)->(b WHERE b.vprop1 > 20)->(a WHERE a.vprop1 between 20 and 2000) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v12  | v21     |      20 |       1010
 v13  | v23     |      30 |       1030
 v22  | v32     |    1020 |       2020
 v23  | v13     |    1030 |         30
(4 rows)

-- labels and elements kinds of element patterns with the same variable name
SELECT * FROM GRAPH_TABLE (g1 MATCH (a IS l1)-[a IS l1]->(b IS l1) COLUMNS (a.ename AS aename, b.ename AS bename)) ORDER BY 1, 2; -- error
ERROR:  element patterns with same variable name "a" but different element pattern types
SELECT * FROM GRAPH_TABLE (g1 MATCH (a IS vl1)->(b)->(a IS vl2) WHERE a.vname <> b.vname COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;  -- error
ERROR:  element patterns with same variable name "a" but different label expressions are not supported
SELECT * FROM GRAPH_TABLE (g1 MATCH (a IS vl1)->(b)->(a) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v12  | v21     |      20 |       1010
 v13  | v23     |      30 |       1030
(2 rows)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)->(b)->(a IS vl1) COLUMNS (a.vname AS self, b.vname AS through, a.vprop1 AS self_p1, b.vprop1 AS through_p1)) ORDER BY self, through;
 self | through | self_p1 | through_p1 
------+---------+---------+------------
 v12  | v21     |      20 |       1010
 v13  | v23     |      30 |       1030
(2 rows)

-- add loop to test edge patterns with same variable name
CREATE TABLE e3_3 (
    src_id int,
    dest_id int,
    ename varchar(10),
    eprop1 int
);
ALTER PROPERTY GRAPH g1
    ADD EDGE TABLES (
        e3_3 KEY (src_id, dest_id)
            SOURCE KEY (src_id) REFERENCES v3 (id)
            DESTINATION KEY (dest_id) REFERENCES v3 (id)
            LABEL el2 PROPERTIES (ename, eprop1 * 10 AS lprop2)
            LABEL l1 PROPERTIES (ename AS elname)
    );
INSERT INTO e3_3 VALUES (2003, 2003, 'e331', 10010);
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
 self | loop_name 
------+-----------
 v33  | e331
(1 row)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(c)-[b]->(d) COLUMNS (a.vname AS aname, b.ename AS bname, c.vname AS cname, d.vname AS dname)); --error
ERROR:  an edge cannot connect more than two vertices even in a cyclic pattern
-- the looping edge should be reported only once even when edge pattern with any direction is used
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[c]-(a) COLUMNS (a.vname AS self, c.ename AS loop_name));
 self | loop_name 
------+-----------
 v33  | e331
(1 row)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-(a) COLUMNS (a.vname AS self));
 self 
------
 v33
(1 row)

-- test explicit and implicit collation assignment
INSERT INTO e3_3 VALUES (2003, 2003, 'E331', 10011);
SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (upper(a.vname) AS self, b.ename AS loop_name)) ORDER BY loop_name COLLATE "C" ASC;
 self | loop_name 
------+-----------
 V33  | E331
 V33  | e331
(2 rows)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b IS el2 WHERE b.ename > 'E331' COLLATE "C"]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name));
 self | loop_name 
------+-----------
 v33  | e331
(1 row)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.ename > 'E331' COLLATE "C" COLUMNS (a.vname AS self, b.ename AS loop_name));
 self | loop_name 
------+-----------
 v33  | e331
(1 row)

SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.vname AS self, b.ename AS loop_name)) WHERE loop_name > 'E331' COLLATE "C";
 self | loop_name 
------+-----------
 v33  | e331
(1 row)

-- property graph with some of the elements, labels and properties same as the
-- previous one. Test whether components from the specified property graph are
-- used. Also test explicit collation specification in property.
CREATE PROPERTY GRAPH g2
    VERTEX TABLES (
        v1
            LABEL l1 PROPERTIES ('g2.' || vname COLLATE "C" AS elname),
        v2 KEY (id1, id2)
            LABEL l1 PROPERTIES ('g2.' || vname COLLATE "C" AS elname),
        v3
            LABEL l1 PROPERTIES ('g2.' || vname COLLATE "C" AS elname)
    )
    EDGE TABLES (
        e1_2 key (id_1, id_2_1, id_2_2)
            SOURCE KEY (id_1) REFERENCES v1 (id)
            DESTINATION KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" AS elname),
        e1_3
            SOURCE KEY (id_1) REFERENCES v1 (id)
            DESTINATION KEY (id_3) REFERENCES v3 (id)
            LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" AS elname),
        e2_3 KEY (id_2_1, id_2_2, id_3)
            SOURCE KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            DESTINATION KEY (id_3) REFERENCES v3 (id)
            LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" AS elname),
        e3_3 KEY (src_id, dest_id)
            SOURCE KEY (src_id) REFERENCES v3 (id)
            DESTINATION KEY (src_id) REFERENCES v3 (id)
            LABEL l1 PROPERTIES ('g2.' || ename COLLATE "C" AS elname)
    );
SELECT sn, cn, dn FROM GRAPH_TABLE (g2 MATCH (src IS l1)-[conn IS l1]->(dest IS l1) COLUMNS (src.elname AS sn, conn.elname AS cn, dest.elname AS dn)) ORDER BY 1, 2, 3;
   sn   |   cn    |   dn   
--------+---------+--------
 g2.v11 | g2.e121 | g2.v22
 g2.v11 | g2.e131 | g2.v33
 g2.v11 | g2.e132 | g2.v31
 g2.v12 | g2.e122 | g2.v21
 g2.v13 | g2.e123 | g2.v23
 g2.v22 | g2.e231 | g2.v32
 g2.v33 | g2.E331 | g2.v33
 g2.v33 | g2.e331 | g2.v33
(8 rows)

SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b WHERE b.elname > 'g2.E331']->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name));
  self  | loop_name 
--------+-----------
 g2.v33 | g2.e331
(1 row)

SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) WHERE b.elname > 'g2.E331' COLUMNS (a.elname AS self, b.elname AS loop_name));
  self  | loop_name 
--------+-----------
 g2.v33 | g2.e331
(1 row)

SELECT * FROM GRAPH_TABLE (g2 MATCH (a)-[b]->(a)-[b]->(a) COLUMNS (a.elname AS self, b.elname AS loop_name)) WHERE loop_name > 'g2.E331';
  self  | loop_name 
--------+-----------
 g2.v33 | g2.e331
(1 row)

-- prepared statements, any changes to the property graph should be reflected in
-- the already prepared statements
PREPARE cyclestmt AS SELECT * FROM GRAPH_TABLE (g1 MATCH (a IS l1)->(b IS l1)->(c IS l1) WHERE a.elname = c.elname COLUMNS (a.elname AS self, b.elname AS through)) ORDER BY self, through;
EXECUTE cyclestmt;
 self | through 
------+---------
 v12  | v21
 v13  | v23
 v21  | v12
 v22  | v32
 v23  | v13
 v32  | v22
 v33  | v33
 v33  | v33
 v33  | v33
 v33  | v33
(10 rows)

ALTER PROPERTY GRAPH g1 DROP EDGE TABLES (e3_2, e3_3);
EXECUTE cyclestmt;
 self | through 
------+---------
 v12  | v21
 v13  | v23
 v21  | v12
 v23  | v13
(4 rows)

ALTER PROPERTY GRAPH g1
    ADD EDGE TABLES (
        e3_2 KEY (id_3, id_2_1, id_2_2)
            SOURCE KEY (id_3) REFERENCES v3 (id)
            DESTINATION KEY (id_2_1, id_2_2) REFERENCES v2 (id1, id2)
            LABEL el2 PROPERTIES (ename, eprop1 * 10 AS lprop2)
            LABEL l1 PROPERTIES (ename AS elname)
    );
EXECUTE cyclestmt;
 self | through 
------+---------
 v12  | v21
 v13  | v23
 v21  | v12
 v22  | v32
 v23  | v13
 v32  | v22
(6 rows)

ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v3 DROP LABEL l1;
EXECUTE cyclestmt;
 self | through 
------+---------
 v12  | v21
 v13  | v23
 v21  | v12
 v23  | v13
(4 rows)

ALTER PROPERTY GRAPH g1 ALTER VERTEX TABLE v3 ADD LABEL l1 PROPERTIES (vname AS elname);
EXECUTE cyclestmt;
 self | through 
------+---------
 v12  | v21
 v13  | v23
 v21  | v12
 v22  | v32
 v23  | v13
 v32  | v22
(6 rows)

ALTER PROPERTY GRAPH g1
    ADD EDGE TABLES (
        e3_3 KEY (src_id, dest_id)
            SOURCE KEY (src_id) REFERENCES v3 (id)
            DESTINATION KEY (src_id) REFERENCES v3 (id)
            LABEL l2 PROPERTIES (ename AS elname)
    );
PREPARE loopstmt AS SELECT * FROM GRAPH_TABLE (g1 MATCH (a)-[e IS l2]->(a) COLUMNS (e.elname AS loop)) ORDER BY loop COLLATE "C" ASC;
EXECUTE loopstmt;
 loop 
------
 E331
 e331
(2 rows)

ALTER PROPERTY GRAPH g1 ALTER EDGE TABLE e3_3 ALTER LABEL l2 DROP PROPERTIES (elname);
EXECUTE loopstmt; -- error
ERROR:  property "elname" for element variable "e" not found
ALTER PROPERTY GRAPH g1 ALTER EDGE TABLE e3_3 ALTER LABEL l2 ADD PROPERTIES ((ename || '_new')::varchar(10) AS elname);
EXECUTE loopstmt;
   loop   
----------
 E331_new
 e331_new
(2 rows)

-- inheritance and partitioning
CREATE TABLE pv (id int, val int);
CREATE TABLE cv1 () INHERITS (pv);
CREATE TABLE cv2 () INHERITS (pv);
INSERT INTO pv VALUES (1, 10);
INSERT INTO cv1 VALUES (2, 20);
INSERT INTO cv2 VALUES (3, 30);
CREATE TABLE pe (id int, src int, dest int, val int, flag boolean);
CREATE TABLE ce1 () INHERITS (pe);
CREATE TABLE ce2 () INHERITS (pe);
INSERT INTO pe VALUES (1, 1, 2, 100, false);
INSERT INTO ce1 VALUES (2, 2, 3, 200, false);
INSERT INTO ce2 VALUES (3, 3, 1, 300, true);
CREATE PROPERTY GRAPH g3
    NODE TABLES (
        pv KEY (id)
    )
    RELATIONSHIP TABLES (
        pe KEY (id)
            SOURCE KEY(src) REFERENCES pv(id)
            DESTINATION KEY(dest) REFERENCES pv(id)
    );
SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe]->(d IS pv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  10 | 100 |  20
  20 | 200 |  30
  30 | 300 |  10
(3 rows)

-- bare property reference in WHERE clause
SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe WHERE e.flag]->(d IS pv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  30 | 300 |  10
(1 row)

SELECT * FROM GRAPH_TABLE (g3 MATCH (s IS pv)-[e IS pe]->(d IS pv) WHERE e.flag COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  30 | 300 |  10
(1 row)

-- temporary property graph
CREATE TEMPORARY PROPERTY GRAPH gtmp
    VERTEX TABLES (
        pv KEY (id)
    )
    EDGE TABLES (
        pe KEY (id)
            SOURCE KEY(src) REFERENCES pv(id)
            DESTINATION KEY(dest) REFERENCES pv(id)
    );
SELECT * FROM GRAPH_TABLE (gtmp MATCH (s IS pv)-[e IS pe]->(d IS pv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  10 | 100 |  20
  20 | 200 |  30
  30 | 300 |  10
(3 rows)

CREATE TABLE ptnv (id int PRIMARY KEY, val int) PARTITION BY LIST(id);
CREATE TABLE prtv1 PARTITION OF ptnv FOR VALUES IN (1, 2);
CREATE TABLE prtv2 PARTITION OF ptnv FOR VALUES IN (3);
INSERT INTO ptnv VALUES (1, 10), (2, 20), (3, 30);
CREATE TABLE ptne (id int PRIMARY KEY, src int REFERENCES ptnv(id), dest int REFERENCES ptnv(id), val int) PARTITION BY LIST(id);
CREATE TABLE ptne1 PARTITION OF ptne FOR VALUES IN (1, 2);
CREATE TABLE ptne2 PARTITION OF ptne FOR VALUES IN (3);
INSERT INTO ptne VALUES (1, 1, 2, 100), (2, 2, 3, 200), (3, 3, 1, 300);
CREATE PROPERTY GRAPH g4
    VERTEX TABLES (ptnv)
    EDGE TABLES (
        ptne
            SOURCE KEY (src) REFERENCES ptnv(id)
            DESTINATION KEY (dest) REFERENCES ptnv(id)
    );
SELECT * FROM GRAPH_TABLE (g4 MATCH (s IS ptnv)-[e IS ptne]->(d IS ptnv) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  10 | 100 |  20
  20 | 200 |  30
  30 | 300 |  10
(3 rows)

-- edges from the same vertex in both directions connecting to other vertices in the same table
SELECT * FROM GRAPH_TABLE (g4 MATCH (s)-[e]-(d) WHERE s.id = 3 COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  30 | 200 |  20
  30 | 300 |  10
(2 rows)

SELECT * FROM GRAPH_TABLE (g4 MATCH (s WHERE s.id = 3)-[e]-(d) COLUMNS (s.val, e.val, d.val)) ORDER BY 1, 2, 3;
 val | val | val 
-----+-----+-----
  30 | 200 |  20
  30 | 300 |  10
(2 rows)

-- GRAPH_TABLE in views
-- The query in the view definition is intentionally complex to test one view with many
-- features like label disjunction, lateral references, WHERE clauses on graph
-- pattern elements as well as on the whole graph pattern.
CREATE VIEW customers_us AS
SELECT g.* FROM x1,
                GRAPH_TABLE (myshop MATCH (c IS customers WHERE c.address = 'US' AND c.customer_id = x1.a)
                                          -[IS customer_orders | customer_wishlists ]->
                                          (l IS orders | wishlists)-[ IS list_items]->(p IS products)
                                    WHERE p.price > 0
                                    COLUMNS (c.name AS customer_name, p.name AS product_name, p.price, x1.a AS a)) g
           ORDER BY customer_name, product_name;
-- Dropping properties or labels used by a view is not allowed
-- If these DDLs succeed, the pg_get_viewdef call below will throw cache lookup
-- error.
ALTER PROPERTY GRAPH myshop ALTER VERTEX TABLE orders DROP LABEL orders; -- error
ERROR:  cannot drop label orders of property graph myshop because other objects depend on it
DETAIL:  view customers_us depends on label orders of property graph myshop
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
ALTER PROPERTY GRAPH myshop ALTER VERTEX TABLE customers
    ALTER LABEL customers DROP PROPERTIES (address);  -- error
ERROR:  cannot drop property address of property graph myshop because other objects depend on it
DETAIL:  view customers_us depends on property address of property graph myshop
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
ALTER PROPERTY GRAPH myshop ALTER VERTEX TABLE products
    ALTER LABEL products DROP PROPERTIES (price);  -- error
ERROR:  cannot drop property price of property graph myshop because other objects depend on it
DETAIL:  view customers_us depends on property price of property graph myshop
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- ruleutils reverse parsing
SELECT pg_get_viewdef('customers_us'::regclass);
                                                                                                                                                                pg_get_viewdef                                                                                                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SELECT g.customer_name,                                                                                                                                                                                                                                                                                                                     +
     g.product_name,                                                                                                                                                                                                                                                                                                                          +
     g.price,                                                                                                                                                                                                                                                                                                                                 +
     g.a                                                                                                                                                                                                                                                                                                                                      +
    FROM x1,                                                                                                                                                                                                                                                                                                                                  +
     GRAPH_TABLE (myshop MATCH (c IS customers WHERE (((c.address)::text = 'US'::text) AND (c.customer_id = x1.a)))-[IS customer_orders|customer_wishlists]->(l IS orders|wishlists)-[IS list_items]->(p IS products) WHERE (p.price > (0)::numeric) COLUMNS (c.name AS customer_name, p.name AS product_name, p.price AS price, x1.a AS a)) g+
   ORDER BY g.customer_name, g.product_name;
(1 row)

-- test view/graph nesting
CREATE VIEW customers_view AS SELECT customer_id, 'redacted' || customer_id AS name_redacted, address FROM customers;
SELECT * FROM customers;
 customer_id |   name    | address 
-------------+-----------+---------
           1 | customer1 | US
           2 | customer2 | CA
           3 | customer3 | GL
(3 rows)

SELECT * FROM customers_view;
 customer_id | name_redacted | address 
-------------+---------------+---------
           1 | redacted1     | US
           2 | redacted2     | CA
           3 | redacted3     | GL
(3 rows)

CREATE PROPERTY GRAPH myshop2
    VERTEX TABLES (
        products,
        customers_view KEY (customer_id) LABEL customers,
        orders
    )
    EDGE TABLES (
        order_items KEY (order_items_id)
            SOURCE KEY (order_id) REFERENCES orders (order_id)
            DESTINATION KEY (product_no) REFERENCES products (product_no),
        customer_orders KEY (customer_orders_id)
            SOURCE KEY (customer_id) REFERENCES customers_view (customer_id)
            DESTINATION KEY (order_id) REFERENCES orders (order_id)
    );
CREATE VIEW customers_us_redacted AS SELECT * FROM GRAPH_TABLE (myshop2 MATCH (c IS customers WHERE c.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (c.name_redacted AS customer_name_redacted));
SELECT * FROM customers_us_redacted;
 customer_name_redacted 
------------------------
 redacted1
(1 row)

-- GRAPH_TABLE in UDFs
CREATE FUNCTION out_degree(sname varchar) RETURNS varchar AS $$
DECLARE
    out_degree int;
BEGIN
    SELECT count(*) INTO out_degree FROM GRAPH_TABLE (g1 MATCH (src WHERE src.vname = sname)->() COLUMNS (src.vname));
    RETURN out_degree;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION direct_connections(sname varchar)
RETURNS TABLE (cname varchar, dname varchar)
AS $$
    SELECT cname, dname FROM GRAPH_TABLE (g1 MATCH (src WHERE src.vname = sname)-[conn]->(dst) COLUMNS (conn.ename AS cname, dst.vname AS dname));
$$ LANGUAGE SQL;
SELECT sname, out_degree(sname) FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname AS sname));
 sname | out_degree 
-------+------------
 v11   | 3
 v12   | 1
 v13   | 1
(3 rows)

SELECT sname, cname, dname FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname AS sname)), LATERAL direct_connections(sname);
 sname | cname | dname 
-------+-------+-------
 v11   | e121  | v22
 v11   | e131  | v33
 v11   | e132  | v31
 v12   | e122  | v21
 v13   | e123  | v23
(5 rows)

-- GRAPH_TABLE joined to a regular table
SELECT * FROM customers co, GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = co.address)-[IS customer_orders]->(o IS orders) COLUMNS (cg.name_redacted AS customer_name_redacted)) WHERE co.customer_id = 1;
 customer_id |   name    | address | customer_name_redacted 
-------------+-----------+---------+------------------------
           1 | customer1 | US      | redacted1
(1 row)

-- graph table in a subquery
SELECT * FROM customers co WHERE co.customer_id = (SELECT customer_id FROM GRAPH_TABLE (myshop2 MATCH (cg IS customers WHERE cg.address = 'US')-[IS customer_orders]->(o IS orders) COLUMNS (cg.customer_id)));
 customer_id |   name    | address 
-------------+-----------+---------
           1 | customer1 | US
(1 row)

-- query within graph table
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE src.vprop1 > (SELECT max(v1.vprop1) FROM v1) COLUMNS(src.vname AS sname, dest.vname AS dname));
ERROR:  subqueries within GRAPH_TABLE reference are not supported
SELECT sname, dname FROM GRAPH_TABLE (g1 MATCH (src)->(dest) WHERE out_degree(src.vname) > (SELECT max(out_degree(nname)) FROM GRAPH_TABLE (g1 MATCH (node) COLUMNS (node.vname AS nname))) COLUMNS(src.vname AS sname, dest.vname AS dname));
ERROR:  subqueries within GRAPH_TABLE reference are not supported
-- GRAPH_TABLE subquery in HAVING clause (tests expression mutator)
SELECT src.vname, count(*) FROM v1 AS src
  GROUP BY src.vname
  HAVING count(*) >= (SELECT count(*) FROM GRAPH_TABLE (g1 MATCH (a IS vl1 | vl2) COLUMNS (a.vname AS n)) WHERE n = src.vname)
  ORDER BY vname;
 vname | count 
-------+-------
 v11   |     1
 v12   |     1
 v13   |     1
(3 rows)

-- Locking clause on GRAPH_TABLE
SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname)) gt FOR UPDATE OF gt;  -- not supported
ERROR:  FOR UPDATE cannot be applied to GRAPH_TABLE
LINE 1: ...MATCH (src IS vl1) COLUMNS (src.vname)) gt FOR UPDATE OF gt;
                                                                    ^
SELECT * FROM GRAPH_TABLE (g1 MATCH (src IS vl1) COLUMNS (src.vname)) gt FOR UPDATE;  -- ignored
 vname 
-------
 v11
 v12
 v13
(3 rows)

-- leave the objects behind for pg_upgrade/pg_dump tests