java - 我无法从 plpgsql 函数中读取 java 返回的数组

Question

Postgres plpgsql 函数:

CREATE OR REPLACE FUNCTION usersList()
RETURNS TABLE(at varchar,name varchar,surname varchar)  AS $$
    BEGIN
        RETURN QUERY SELECT * FROM users;
    END;
$$ LANGUAGE plpgsql;

和java代码

result = Pstatement.executeQuery("Select usersList() ");
while(result.next()) {
    System.out.println(result.getString(("at")));
    System.out.println(result.getString(("name")));
    System.out.println(result.getString(("surname")));
}

Java错误sql异常信息:

Message: The column name at was not found in this ResultSet.
SQLState: 42703
ErrorCode: 0

如何从一个函数返回所有表列，然后在 java 中打印它们？

Answer 1

Postgres plpgsql 函数:

CREATE OR REPLACE FUNCTION usersList()
RETURNS TABLE(at varchar,name varchar,surname varchar)  AS $$
    BEGIN
        RETURN QUERY SELECT * FROM users;
    END;
$$ LANGUAGE plpgsql;

和java代码

result = Pstatement.executeQuery("SELECT * FROM usersList() ");
while(result.next()) {
    System.out.println(result.getString(("at")));
    System.out.println(result.getString(("name")));
    System.out.println(result.getString(("surname")));
}

致谢 RealSkeptic && Nick Barnes !!!