MonetDB Prepare Statement in Function

Question

I'm trying to create a function that takes the parameters for the column, the table, the limit, and offset. Basically, I want to be able to get a specified number of rows data from a specified table from a specified column.

However, I'm unable to get the following code to work - I get several errors such as:

syntax error, unexpected SELECT, expecting ':' in: "create function get_banana(lim int, off int, tbl varchar(32), col varchar(32)) r"
syntax error, unexpected RETURN in: "return"
syntax error, unexpected END in: "end"

These errors seem kind of meaningless.

My code is as follows:

CREATE FUNCTION GET_BANANA(lim int, off int, tbl varchar(32), col varchar(32)) 
RETURNS TABLE (clm int) 
BEGIN 
    PREPARE SELECT col FROM tbl LIMIT ? OFFSET ?; 
    RETURN EXEC (lim, off); 
END;

I'd appreciate any help :) Thanks!

cornuz cornuz · Accepted Answer · 2016-10-07T12:05:20

I see at least two issues

EXEC needs the identifier that is returned by PREPARE, e.g.:

sql>prepare select * from tables;
execute prepared statement using: EXEC 2(...)
sql>exec 2();

The function parameters tbl and col are string values. You cannot use them as table/column identifiers.

Having said that, I am not even sure if PREPARE can be used inside a function.

MonetDB Prepare Statement in Function

2 Answers