Apache AGE Cypher query in dynamic PL/pgSQL functions

Question

I'm using the official release from Apache AGE GitHub for PostgreSQL 14 installed via homebrew on a Mac. I've run into an issue related to creating functions with Cypher and PL/pgSQL.

I'm trying to create a node with data that's already getting read in correctly and then I would like to receive that node's id so I can reference it in a Postgres table column.

CREATE OR REPLACE FUNCTION Reader (
    -- Bar Parameters
    p_bar_name VARCHAR,
    p_bar_tag VARCHAR,
    p_bar_city VARCHAR,
    p_bar_state VARCHAR,
    p_bar_address VARCHAR,
    p_bar_country VARCHAR,
    p_tag_image VARCHAR,
    p_bar_image VARCHAR,
    p_bar_email VARCHAR
)
RETURNS agtype AS $$
DECLARE new_id agtype;
BEGIN
    SELECT * FROM cypher('algorithm', $$
        CREATE (n:Bar {
            bar_name: $p_bar_name,
            bar_tag: $p_bar_tag,
            bar_city: $p_bar_city,
            bar_state: $p_bar_state,
            bar_address: $p_bar_address,
            bar_country: $p_bar_country,
            tag_image: $p_tag_image,
            bar_image: $p_bar_image,
            bar_email: $p_bar_email
        })
        RETURN id(b)
    $$) AS (new_id agtype);
    RETURN new_id;
END;
$$ LANGUAGE plcypher;

and here is my error...

ERROR:  syntax error at or near "CREATE"
LINE 18:         CREATE (n:Bar {
                 ^ 
SQL state: 42601
Character: 388

I tried the Apache AGE documentation but I couldn't find much.

I tried changing my statement to

CREATE OR REPLACE FUNCTION Reader (
    -- Bar Parameters
    p_bar_name VARCHAR,
    p_bar_tag VARCHAR,
    p_bar_city VARCHAR,
    p_bar_state VARCHAR,
    p_bar_address VARCHAR,
    p_bar_country VARCHAR,
    p_tag_image VARCHAR,
    p_bar_image VARCHAR,
    p_bar_email VARCHAR
)
RETURNS ag_catalog.graphid AS $$
DECLARE
    new_id ag_catalog.graphid;
BEGIN
    EXECUTE format('SELECT (properties->''id'')::bigint::ag_catalog.graphid as id 
                    FROM ag_catalog.cypher(''algorithm'', $create$ 
                    CREATE (n:Bar {
                        bar_name: $1,
                        bar_tag: $2,
                        bar_city: $3,
                        bar_state: $4,
                        bar_address: $5,
                        bar_country: $6,
                        tag_image: $7,
                        bar_image: $8,
                        bar_email: $9
                    }) RETURN n $create$) AS (properties algorithm.ag_catalog.agtype)')
    INTO new_id
    USING p_bar_name, p_bar_tag, p_bar_city, p_bar_state, p_bar_address, p_bar_country, p_tag_image, p_bar_image, p_bar_email;

    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

but now I'm getting

SQL Error: ERROR: cross-database references are not implemented: algorithm.ag_catalog.agtype
  Where: SQL statement "SELECT (properties->'id')::bigint::ag_catalog.graphid as id 
                    FROM ag_catalog.cypher('algorithm', $create$ 
                    CREATE (n:Bar {
                        bar_name: $1,
                        bar_tag: $2,
                        bar_city: $3,
                        bar_state: $4,
                        bar_address: $5,
                        bar_country: $6,
                        tag_image: $7,
                        bar_image: $8,
                        bar_email: $9
                    }) RETURN n $create$) AS (properties algorithm.ag_catalog.agtype)"

Answer 1

The two error messages you got are unrelated. As pointed out by @JohnH, you mismatched your dollar quotes.

CREATE OR REPLACE FUNCTION Reader (
    -- Bar Parameters
)
RETURNS agtype AS $$--opening quotes
DECLARE new_id agtype;
BEGIN
    SELECT * FROM cypher('algorithm', $$--this closes the function body
    CREATE (n:Bar {--...all that was here ended up outside function body 
    $$--this opens up another quoted block
    ) AS (new_id agtype);
    RETURN new_id;
END;
$$ --this closes the second quoted block
LANGUAGE plcypher;--there's no `plcypher` language in Apache AGE or standard PostgreSQL

Re-using $$ for the two distinct quoted blocks confused the parser into closing the function body quotes too soon. As you found, you can solve that by switching one of the quoted blocks to single quotes ' and duplicate them every time you need to use a single quote inside the already single-quoted block.
It's a bit more convenient to use named dollar quotes $name$ and you can nest as many as you want - no need to mix in the singles for anything else than constants:

CREATE OR REPLACE FUNCTION Reader (
    -- Bar Parameters
)
RETURNS ag_catalog.graphid AS 
$function_body$
DECLARE new_id ag_catalog.graphid;
BEGIN EXECUTE format(
    $dynamic_sql$
    SELECT (properties->'id')::bigint::ag_catalog.graphid as id 
    FROM ag_catalog.cypher('algorithm', 
            $create$ 
            CREATE (n:Bar {
                        bar_name: $1,
                        bar_tag: $2,
                        bar_city: $3,
                        bar_state: $4,
                        bar_address: $5,
                        bar_country: $6,
                        tag_image: $7,
                        bar_image: $8,
                        bar_email: $9
                    }) 
            RETURN n 
            $create$) AS (properties ag_catalog.agtype)--here
    $dynamic_sql$)
    INTO new_id
    USING p_bar_name, p_bar_tag, p_bar_city, p_bar_state
        , p_bar_address, p_bar_country, p_tag_image, p_bar_image
        , p_bar_email;
    RETURN new_id;
END;
$function_body$ LANGUAGE plpgsql;

Your second error is also the parser complaining: algorithm.ag_catalog.agtype is assumed to mean a cross-database reference: it's looking for agtype in the namespace ag_catalog in the database algorithm. If you're doing this on a database with Apache AGE installed, the ag_catalog.agtype is available directly, without specifying the database. Even if algorithm was the name of the one you're currently working on, just skip it.