Hi Babis, this works fine with me with MonetDB Aug2011-SP3: sql>create table r (u varchar(32), v varchar(32), r varchar(5)); operation successful (192.387ms) sql>CREATE FUNCTION refine () more>RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) more>BEGIN more> RETURN TABLE ( more> more> ( SELECT R.u, R.v, R.r FROM R more> more> INTERSECT more> more> SELECT R.u, R.v, R.r FROM R more> ) more> ); more>END; operation successful (87.109ms) sql>select * from refine(); +---+---+---+ | u | v | r | +===+===+===+ +---+---+---+ 0 tuples (73.457ms) sql>drop function refine; operation successful (71.159ms) sql>select * from refine(); SELECT: no such operator 'refine' As you can also see above, the prober way to drop a function definitions to use (AFAIK) standard SQL "DROP FUNCTION <functionname>;". Stefan On Sat, Dec 17, 2011 at 01:23:08PM +0200, 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
------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |