I can't read in java returned array from plpgsql function

Question

Postgres plpgsql function :

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

And java code

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 error sql exception message :

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

How can i return all table columns from a function and then print them in java ?

Yep, SELECT usersList() treats usersList() as a field, and packs the output columns into a single value (of type record). SELECT * FROM usersList() treats usersList() as a table, and gives you the result you're expecting. — Nick Barnes
– Nick Barnes, Commented Apr 10, 2015 at 12:40
Its really works.Thnx you. Added as answer to vote it as the right one !!! — gtzinos
– gtzinos, Commented Apr 10, 2015 at 12:42

gtzinos · Accepted Answer · 2015-04-10 12:49:03Z

1

Postgres plpgsql function :

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

And java code

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")));
}

Credits to RealSkeptic && Nick Barnes !!!

answered Apr 10, 2015 at 12:49

gtzinos

1,19715 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ceiling Gecko · Accepted Answer · 2015-04-10 12:28:50Z

0

You can use result.getMetaData().getColumnCount() to see how many columns you retrieved and then you can call result.getMetaData().getColumnName(x) to check the name of the column (replace x with the number of column).

So technically you should be able to write your code block like so:

result = Pstatement.executeQuery("Select usersList() ");
String mesites[];
while(result.next()) {
    int columnCount = result.getMetaData().getColumnCount();
    System.out.println("Found:"+columnCount+" columns.");
    for(int i=1; i<=columnCount; i++){
        System.out.println(result.getString(result.getMetaData().getColumnName(i)));
    }
}

Which should then print out any columns retrieved in that result set regardless of names (if any).

answered Apr 10, 2015 at 12:28

Ceiling Gecko

3,1962 gold badges29 silver badges34 bronze badges

1 Comment

gtzinos Over a year ago

Found:1 columns. (" at1",name1,surname1) Found:1 columns. (" 123456",george,gtest) . . .

Collectives™ on Stack Overflow

I can't read in java returned array from plpgsql function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related