How to assign cypher result to pgsql variable?

[NathanFrund]

Hello!

I have a pretty basic cypher function whose result I'd like to assign to a pgsql variable.

Here is the mentioned query.

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT exists
FROM cypher('test_graph', $$
    MATCH (n:Node)
    RETURN exists((n)-[:START]->())
$$) as (exists agtype);

I'm stumped.

In a way, I hope that the answer is obvious, but not too obvious!

[NathanFrund]

In the event that someone else has the same question:

https://age.apache.org/age-manual/master/advanced/plpgsql.html

[MironAtHome]

Hello!

I have a pretty basic cypher function whose result I'd like to assign to a pgsql variable.

Here is the mentioned query.

LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT exists
FROM cypher('test_graph', $$
    MATCH (n:Node)
    RETURN exists((n)-[:START]->())
$$) as (exists agtype);

I'm stumped.

In a way, I hope that the answer is obvious, but not too obvious!

Here is example:

LOAD 'age';
SET search_path = ag_catalog, "$user", public;
DO
$RUN$
DECLARE
    Output_Var jsonb;
BEGIN
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

SELECT INTO Output_Var
         array_to_json(array_agg(jsonb_build_object(
        'existscheck', t.existscheck
       , 'key', t.id)))
FROM cypher('test_graph', $$
    MATCH (n:Node)
    OPTIONAL MATCH (n)-[e:START]->(u)
    WITH ag_catalog.agtype_to_bool(exists((n)-[e]->(u))) AS existscheck, id(n) as id
    RETURN existscheck, id
   ORDER BY id ASC
   LIMIT 10
$$) as t(existscheck boolean, id agtype);
RAISE NOTICE 'Output_Var : %', Output_Var;
END;
$RUN$;

this helps to package output of a query, any query for that matter, into a jsonb document, that can be passed further. Next step is to query json. Here is example of next step, assuming output of query above looks like this:

'[
 {"key":1, "existscheck":true}
,{"key":2, "existscheck":false}
,{"key":3, "existscheck":false}
,{"key":4, "existscheck":false}
,{"key":5, "existscheck":false}
,{"key":6, "existscheck":false}
,{"key":7, "existscheck":false}
,{"key":8, "existscheck":false}
,{"key":9, "existscheck":false}
,{"key":10, "existscheck":false}
]'

here is query:

WITH JsonObjectTable AS (
	SELECT j
	FROM jsonb_array_elements('[{"key":1, "existscheck":true},{"key":2, "existscheck":false},{"key":3, "existscheck":false},{"key":4, "existscheck":false},{"key":5, "existscheck":false},{"key":6, "existscheck":false},{"key":7, "existscheck":false},{"key":8, "existscheck":false},{"key":9, "existscheck":false},{"key":10, "existscheck":false}]') AS t(j)
), FieldDecodedTable AS (
	SELECT FieldValues."Key"
		 , FieldValues."ExistsCheck"
	FROM JsonObjectTable
	JOIN LATERAL (
	    SELECT j->>'key' AS "Key"
		     , j->>'existscheck' AS "ExistsCheck"
	) AS FieldValues ON TRUE
)
    SELECT *
	FROM FieldDecodedTable;

Hope it helps. --cheers

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove "Abondoned" label or comment or this will be closed in 14 days.

[github-actions[bot]]

This issue was closed because it has been stalled for further 14 days with no activity.