Apache AGE. PREPARE. ERROR: function cypher_stored_procedure(unknown) does not exist

Question

According to instructions Apache AGE docs wrote a function:

CREATE OR REPLACE FUNCTION sh_univeronto.f_example() RETURNS TABLE(res ag_catalog.agtype) LANGUAGE plpgsql AS $function$
    begin
        LOAD 'age';
        SET search_path TO ag_catalog, public;

        PREPARE cypher_stored_procedure(ag_catalog.agtype) as
        SELECT *
        FROM ag_catalog.cypher('sh_univeronto', $$
            MATCH (v:gr_main)           
            WHERE v.unoid = $unoid_val
            RETURN v
        $$, $1)
        AS (v agtype);  
        EXECUTE cypher_stored_procedure('{"unoid_val": "222eee33"}');

        -- other code

    END;
$function$;

Error_1) If I call 1st time: SELECT sh_univeronto.f_example(); gives an error message:

SQL Error [42883]: ERROR: function cypher_stored_procedure(unknown) does not exist No function matches the given name and argument types. You might need to add explicit type casts. PL/pgSQL function sh_univeronto.f_example() line 42 at EXECUTE

Error_2) If I call for the 2nd time, it gives an error:

SQL Error [42P05]: ERROR: prepared statement "cypher_stored_procedure" already exists SQL statement "PREPARE cypher_stored_procedure(ag_catalog.agtype) as SELECT * FROM ag_catalog.cypher('sh_univeronto', $$ MATCH (v:gr_main)
WHERE v.unoid = $unoid_val RETURN v $$, $1) AS (v agtype)" PL/pgSQL function sh_univeronto.f_example() line 33 at SQL statement

If I add DEALLOCATE ALL; then again Error_1 occurs

About Error_2 found recommendations about PgBouncer:

1. Configure the PostgreSQL "Pooling mode" to "session"
2. To cancel the session state, use the option server_reset_query in configuration PgBouncer - in file pgbouncer.ini

But I don't have PgBouncer on the server. I didn't find pgbouncer.ini

How to eliminate Error_1 and Error_2?

Answer 1

I think every time you execute SELECT sh_univeronto.f_example();, you make a prepared statement with PREPARE cypher_stored_procedure(ag_catalog.agtype) ..., that's why you get the error "cypher_stored_procedure" already exists".

Besides, the error 1 might be happening because the PREPARE statement is not yet stored when you call EXECUTE.

So you should put and call the PREPARE statement outside the function sh_univeronto.f_example(), and inside the function just the EXECUTE statement.

Please let me know if this helps.

Answer 2

in the same context as the above answer the prepared statement should only be declared once or

ERROR: prepared statement "cypher_stored_procedure" already exists

there are actually two main things you should take care why we call the prep outside the function and just execute it inside 1- to avoid every time you call the function the above error 2- inside the function, it is not guaranteed that the prep will be executed first it is up to the database engine as it is not promising code. which means you might end up with the database trying to execute before preparing the statement.

PREPARE cypher_stored_procedure(ag_catalog.agtype) as 
CREATE OR REPLACE FUNCTION sh_univeronto.f_example() RETURNS TABLE(res ag_catalog.agtype) LANGUAGE plpgsql AS $function$
    begin
        LOAD 'age';
        SET search_path TO ag_catalog, public;

        SELECT *
        FROM ag_catalog.cypher('sh_univeronto', $$
            MATCH (v:gr_main)           
            WHERE v.unoid = $unoid_val
            RETURN v
        $$, $1)
        AS (v agtype);  
        EXECUTE cypher_stored_procedure('{"unoid_val": "222eee33"}');

        -- other code

    END;
$function$;
-- when you are done to avoid memory leakage 
DEALLOCATE cypher_stored_procedure;