Error while parsing the function using a CURSOR in PLPGSQL

[priyanshi-yb]

Example

stmt := `
CREATE OR REPLACE FUNCTION fetch_film_titles_and_years(
   OUT p_title VARCHAR(255), 
   OUT p_release_year INTEGER
)
RETURNS SETOF RECORD AS
$$
DECLARE
    film_cursor CURSOR FOR
        SELECT title, release_year
        FROM film;
    film_record RECORD;
BEGIN
    -- Open cursor
    OPEN film_cursor;

    -- Fetch rows and return
    LOOP
        FETCH NEXT FROM film_cursor INTO film_record;
        EXIT WHEN NOT FOUND;

        p_title = film_record.title;
        p_release_year = film_record.release_year;
        RETURN NEXT;
    END LOOP;

    -- Close cursor
    CLOSE film_cursor;
END;
$$
LANGUAGE PLPGSQL;

    `
jsonStr, err := pg_query.ParsePlPgSqlToJSON(stmt)
if err != nil {
     fmt.Printf("failed to parse the stmt %v: %v", stmt, err)
}

error

failed to parse the stmt <stmt> : variable "film_cursor" must be of type cursor or refcursor

But this works on PostgreSQL -

postgres=> CREATE OR REPLACE FUNCTION fetch_film_titles_and_years(
   OUT p_title VARCHAR(255), 
   OUT p_release_year INTEGER
)
RETURNS SETOF RECORD AS
$$
DECLARE
    film_cursor CURSOR FOR
        SELECT title, release_year
        FROM film;
    film_record RECORD;
BEGIN
    -- Open cursor
    OPEN film_cursor;

    -- Fetch rows and return
    LOOP
        FETCH NEXT FROM film_cursor INTO film_record;
        EXIT WHEN NOT FOUND;

        p_title = film_record.title;
        p_release_year = film_record.release_year;
        RETURN NEXT;
    END LOOP;

    -- Close cursor
    CLOSE film_cursor;
END;
$$
LANGUAGE PLPGSQL;
CREATE FUNCTION
postgres=>
postgres=> SELECT * FROM fetch_film_titles_and_years();
 p_title | p_release_year 
---------+----------------
 ABC   |           2001
 XYZ   |           2002
(2 rows)

postgres=> 

Cc: @lfittl

[sanyamsinghal]

Hello @lfittl,

First of all, thank you for the excellent work on the pg_query_go module.

I was wondering if there are any plans or a roadmap to have ParsePlPgSql() return a Go struct in the future. I looked into the code and noticed that the underlying libpg_query C library doesn’t currently offer functionality to return a protobuf tree for PL/pgSQL statements, so I understand this might involve significant work.

Additionally, I see that ParsePlPgSqlToJson() is currently marked as experimental. Are there plans to make it production-ready soon?

Any information you can provide would be greatly appreciated. We plan to use the pg_query_go module extensively in our product, YugabyteDB Voyager. You can find our GitHub repository here: https://github.com/yugabyte/yb-voyager.

[h3n4l]

119 This pr introduce the upstream libpgquery changes, and should solve the cursor type parsing problem I thought.