CODEWITHSUNDEEP
postgresqlapache-age
Wendel
Wendel

Reputation: 650

What's the correct typecast for a variable inside this 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

Upvotes: 1

Views: 137

Answers (2)

Muneeb Khan
Muneeb Khan

Reputation: 273

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.

Upvotes: 1

Matheus Farias
Matheus Farias

Reputation: 715

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.

Upvotes: -1

Related Questions