Search code examples
stored-proceduresmonetdb

MonetDB stored procedure return table

From MonetDB-user's:

You cannot use an ordinary select query in a procedure. You can change the contents of tables or set variables, but you cannot use a query like this. Remember, with such a query, there is a result, and where should the result go?

What's the correct way on MonetDB to create kind of:

CREATE XXXX
BEGIN
   SELECT * FROM table;
END

Thanks

Solution

  • This seems more like a job for a VIEW, e.g. 

    CREATE VIEW XXXX AS SELECT * FROM table;

SELECT * FROM XXXX;

    If you want to create a function, you can do this:

    CREATE FUNCTION XXXX() 
RETURNS TABLE (name string)
RETURN TABLE (SELECT name from tables);

SELECT * FROM XXXX();

    Note that in the second case, you need to specify the schema of the returned table in the function definition.