CODEWITHSUNDEEP
databasestored-proceduresinformix
paulhudson
paulhudson

Reputation: 268

Error creating an Informix function

I am trying to create the following function, but its not compiling successfully. I am not sure where the problem is. This is the first time I am trying this one. Can someone please point out the error?

CREATE FUNCTION resources:get_developers (proj_id varchar(10))

    RETURNING varchar(50);
    DEFINE developers varchar(200);
    DEFINE uid varchar(15);
    DEFINE dev_name varchar(50);
    FOREACH cursor1 FOR
        select dev_user_id into uid from proj_dev_map where project_id = proj_id;
        select user_name into dev_name from user where user_attuid = uid;
        LET developers = developers || dev_name || ', ';
    END FOREACH
    RETURN developers;
END FUNCTION

I am just trying the get the names associated with the given proj_id as comma separated values.

Upvotes: 2

Views: 2484

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

Although you can write singleton SELECT statements, you should combine these two into a single query anyway, doubly so since you're only interested in the name and not the uid value.

CREATE FUNCTION get_developers (proj_id varchar(10))
    RETURNING varchar(50);

    DEFINE developers varchar(200);
    DEFINE dev_name varchar(50);

    FOREACH SELECT u.user_name
              INTO dev_name
              FROM proj_dev_map AS p JOIN user AS u ON u.uid = p.user_attuid
             WHERE p.project_id = proj_id
        LET developers = developers || dev_name || ', ';
    END FOREACH

    RETURN developers;

END FUNCTION

Ths SPL syntax is weird/fussy about semi-colons, too. You must not have a semi-colon after the SELECT statement (before the LET). You may have one after the END FOREACH. The code above compiled on my database. I omitted the database name since you can't create functions in anything except the current database (I tried and Informix objected to the notation — not to the fact that I don't have a database called resources). You don't need the cursor name in the FOREACH unless you are going to do a DELETE FROM proj_dev_map WHERE CURRENT OF cursor1 or UPDATE proj_dev_map SET ... WHERE CURRENT OF cursor1, though it does no real harm.

The minimal change to your code to get it to compile (in the resources database) is:

CREATE FUNCTION resources:get_developers (proj_id varchar(10))
    RETURNING varchar(50);

    DEFINE developers varchar(200);
    DEFINE uid varchar(15);
    DEFINE dev_name varchar(50);
    FOREACH cursor1 FOR
        select dev_user_id into uid from proj_dev_map where project_id = proj_id -- ;
        select user_name into dev_name from user where user_attuid = uid;
        LET developers = developers || dev_name || ', ';
    END FOREACH
    RETURN developers;
END FUNCTION

The difference is the commented-out semi-colon after the first SELECT.

Upvotes: 4

Related Questions