Re: [MonetDB-users] SQLException:SQLengine:Program contains errors

17 Dec 2011

      Just to add the full error that I forgot to post earlier:

sql>select * from refine();
SyntaxException:user.refine[0]:RETURN missing
TypeException:user.s2_1[5]:'user.refine' undefined in:
(_7:bat[:oid,:str], _8:bat[:oid,:str], _9:bat[:oid,:str]) :=
user.refine()
SQLException:SQLengine:Program contains errors

Babis

On Sat, Dec 17, 2011 at 1:23 PM, Charalampos Nikolaou  wrote:
...
Hi,
I am getting the error "SQLException:SQLengine:Program contains
errors" when I define a simple function that returns a table. The SQL
reference on the site of MonetDB
(http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol)
states that the accepted expression to a "TABLE" expression should be
"select_no_parens_orderby" which is not defined in any place (or
haven't found it) other than sql_parser.y. The function I define is
the following and apart from the fact that is inserted successfully
into the "functions" table, I checked that it can be produced by the
grammar in sql_parser.y:
CREATE FUNCTION refine ()
RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5))
BEGIN
       RETURN TABLE (
                       (       SELECT R.u, R.v, R.r FROM R
                               INTERSECT
                               SELECT R.u, R.v, R.r FROM R
                       )
               );
END;
Further, what is the proper way to deleting a function, in order to
re-define it in the same session? I observed that is not sufficient to
just delete it from sys.functions table. An entry #function_name is
also present in sys._tables.
Thanks,
Babis