Question 170154 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

What typecast should I use for a variable in an Ubuntu query?

I was writing this cypher query as a function:

CREATE OR REPLACE FUNCTION public.count_friends(name agtype) 
    RETURNS agtype AS $function$
    BEGIN
        SELECT * FROM cypher('graph_name', $$
        MATCH (v:Person)-[]-(w)
        WHERE v.name = name
        RETURN COUNT(w) AS friends
    $$) AS (friends agtype);
    END;
$function$ LANGUAGE plpgsql;

And I was using this select query for testing:

SELECT * FROM cypher('graph_name', $$
        MATCH (v:Person)
      WHERE public.count_friends(v.name) > 3
      RETURN v.name
$$) AS (name agtype);

But, I got an error while executing it:

ERROR:  could not find rte for name
LINE 3:         WHERE v.name = name

What’s the correct typecast for the variable name in this case? Or, is there something wrong with my code?

Environment: Apache AGE 1.3.0 release, PostgreSQL 13.10, Ubuntu 22.04

Tags:

2

Answers


  1. 0

    The error you are getting might be because the number that is compared in the WHERE clause for the testing might be higher than the number of available vertices (WHERE public.count_friends(v.name) > 3).

    First, I created a graph to store the Person vertices with the name property.

    demo=# SELECT create_graph('graph_name');
NOTICE:  graph "graph_name" has been created
 create_graph 
--------------
 
(1 row)

demo=# SELECT * FROM cypher('graph_name', $$
CREATE (a:Person {name: 'Wendel'}), (b:Person {name: 'Matheus'})
RETURN a, b
$$) AS (a agtype, b agtype);
                                          a                                           |                                           b                                           
--------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------
 {"id": 844424930131969, "label": "Person", "properties": {"name": "Wendel"}}::vertex | {"id": 844424930131970, "label": "Person", "properties": {"name": "Matheus"}}::vertex
(1 row)

    After this, created the function:

    CREATE OR REPLACE FUNCTION public.count_friends(name agtype) 
    RETURNS agtype AS $function$
    BEGIN
        SELECT * FROM cypher('graph_name', $$
        MATCH (v:Person)-[]-(w)
        WHERE v.name = name
        RETURN COUNT(w) AS friends
    $$) AS (friends agtype);
    END;
$function$ LANGUAGE plpgsql;

    And then executed the test query without the WHERE clause:

    demo=# SELECT * FROM cypher('graph_name', $$
      MATCH (v:Person)
      RETURN v.name                    
$$) AS (name agtype);

   name    
-----------
 "Wendel"
 "Matheus"
(2 rows)

    It worked fine this way.

    Login or Signup to reply.
  2. 0

    There is an issue with public.count_friends(name agtype) function.
    Study this answer to get understand how to return the result from a query inside PostgreSQL function:

    https://stackoverflow.com/a/7945958/20972645

    Also, the comparison of variables taken as arguments of function with any variable declared or taken by any query within that function gives type miss match errors. Like:

    DETAIL:  Expected agtype value

    For now, you need to find a way around to achieve this functionality.

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

Back To Top
Search