The Toad Data Modeler (TDM) 8.0 Parser does not support ‘return query execute’ for Postgres functions in v16
A function like this cannot be parsed, although it works correctly in Postgres (e.g. creating it in PgAdmin works fine).
To Replicate:
1. Create a Postgres12 DB model in TDM
2. Add a Function, and in the SQL code, paste:
CREATE OR REPLACE FUNCTION "testfunc"()
RETURNS table (a text, b text)
LANGUAGE plpgsql
VOLATILE
AS
$$
DECLARE
_stmt text;
BEGIN
_stmt := 'select ''A'', ''B''';
return query execute _stmt;
END
$$
;
Click Apply
3. The TDM parser shows this exception error in red:
ERROR: line 13, column 32, ending line 13, column 32: Found ';': This statement may be ambiguous, please report it to Quest Software
Parser: PGSQL, on line: 1, context: CREATE OR REPLACE FUNCTION "testfunc"()
RETURNS table (a text, b text)
LANGUAGE plpgsql
VOLATILE
AS
$$
DECLARE
_stmt text;
BEGIN
_stmt := 'select ''A'', ''B''';
return query execute _stmt;
END
$$
;
As of TDM 8.0, Postgresql 16 is not supported, only up to version 12.
WORKAROUND:
In the Function Properties | General tab screen, if you set the Language to pgAdmin or psql it is possible to add the function in the TDM model without this error. However, when using psql or pgAdmin as language identifier, the function does not compile in Postgresql12.
STATUS:
Waiting for fix in a future release of Toad Data Modeler.
