Hello. I have a problem with functions. I need to create a function where i give the params, table name, schema name and field name. Is a function that will go get the value of a field of parameter.

Example:
CREATE FUNCTION "SCHEMA_FUNC".FNC_GET_ID (
SCHEMA_NAME VARCHAR(50),
TABLE_NAME VARCHAR(50),
FIELD_NAME VARCHAR(50))
RETURNS TABLE ( FIELD_NAME INTEGER )
RETURN SELECT FIELD_NAME
FROM SCHEMA_NAME.TABLE_NAME
COMMIT;

SELECT * FROM TABLE("SCHEMA_FUNC".FNC_GET_ID('TEST', 'USER', 'ID_USER'))

Thank you.

You cannot do that directly with a SQL function. You can try doing the "query" in a stored procedure and then wrap the SP with the function.

Andy

What is the advantage of (a) over (b)?

(a) SELECT * FROM TABLE("SCHEMA_FUNC".FNC_GET_ID('TEST', 'USER', 'ID_USER'))

(b) SELECT ID_USER FROM TEST.USER

It was just an example code that I quoted. The idea is to reuse code. When there is a need for maintenance, the idea is to have to make corrections in just one place. Another example would be the following:

CREATE FUNCTION "SCHEMA_FUNC".FNC_GET_ID (
SCHEMA_NAME VARCHAR(50),
TABLE_NAME VARCHAR(50),
FIELD_NAME VARCHAR(50))
RETURNS TABLE ( FIELD_NAME INTEGER )
RETURN SELECT MAX(FIELD_NAME)+1
FROM SCHEMA_NAME.TABLE_NAME
COMMIT;

SELECT * FROM TABLE("SCHEMA_FUNC".FNC_GET_ID('TEST', 'USER', 'ID_USER'))

My system currently has over 2000 tables. And if for some reason I wanted to be the id MAX + 2? I would have too big a job, having to go through all the tables to correct.

won't it be better to put max+1 in as a scalar function, rather than trying to call udf which calls stored procs.