Question 89043 in PostgreSQL Question posted in
The official documentation can be found here.

How to Access Index of Array in the properties of AGE Vertex When passed to PG_Catalog Function – Postgresql

I have created a vertex student:

test=# SELECT * FROM cypher('graph', $$
CREATE (s:student{name:"Muneeb", courses: ["OOP", "DS", "Programming", "Android"]})
 RETURN s
$$) as (student agtype);

----------------------------------------------------------------------------------------

     student    
----------------------------------------------------------------------------------------     

{"id": 3377699720527873, "label": "student", "properties": {"name": "Muneeb", "courses": ["OOP", "DS", "Programming", "Android"]}}::vertex
    (1 row)

It contains courses in its property, which has list of courses in it.

I have created a function that should return 2nd index of the List, in PG_Catalog.

test=# CREATE OR REPLACE FUNCTION pg_catalog.second_in_list(list agtype)
RETURNS agtype AS $$
BEGIN
   return list[1];
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

When I call this function:

test=# SELECT * FROM cypher('graph', $$
Match (s:student) Return pg_catalog.second_in_list(s.courses)
$$) as (student agtype);

I am getting this error:

ERROR:  cannot subscript type agtype because it is not an array
CONTEXT:  SQL statement "SELECT list[1]"
PL/pgSQL function second_in_list(agtype) line 3 at RETURN

I have tried passing list agtype[] as argument to pg_catalog function but it also not work.

Tags:

2

Answers


  1. 0

    The problem is in the custom function you have created you can’t access list item as its type is agtype and to access the second element of the array you need to run a cypher query instead of plain sql query. So either don’t create a custom function and directly access the element as given below:

    SELECT * FROM cypher('graph', $$
Match (s:student) Return s.courses[1]
$$) as (student agtype);

    or add the query itself in the

    CREATE OR REPLACE FUNCTION pg_catalog.second_in_list()
RETURNS TABLE(course agtype)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    RETURN QUERY
        SELECT * FROM cypher('graph', $$ Return s.courses[1] $$) as (student agtype);
END
$BODY$;
$$ LANGUAGE plpgsql;

    and then use

    Select * From second_in_list();

    to get the desired result

    Login or Signup to reply.
  2. 0

    You can achieve that through accessing the agtype list using the -> operator

    test=# CREATE OR REPLACE FUNCTION pg_catalog.second_in_list(list agtype)
RETURNS agtype AS $$
BEGIN
   return list->1;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

    While keeping rest of your code as it

    In AGE, -> is the operator used to access elements of an AGE array by index or keys of an AGE JSON object.

    The reason your function doesn’t work is that it tries to use the subscript operator [] to access the first element (index 1) of an agtype object, which is not an array. That operation is invalid and leads to the error you saw.

    In contrast, using the -> operator, which is used to navigate the graph and retrieve values from a vertex or an edge property in Apache AGE. In this case, when you call list->1, it returns the value associated with the key 1 from the agtype object. The -> operator is the correct operator to use when you want to retrieve a value from a dictionary-like structure like an agtype.

    So, in summary, -> is the right operator to retrieve a value from a dictionary-like structure, as long as that agtype is non-array structure, which is invalid to use [] at SQL query.

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search