Postgres error: a column definition list is only allowed for functions returning "record"

Question

public class DimensionStoneDaoHibernate extends HibernateDaoSupport implements DimensionStoneDao 
      {

    public Connection con = null;
    int i=0;

    public void setCon(Connection con) {
        this.con = getSession().connection(); 
    }

    public Connection getCon() {        

        setCon(con);        
        return con;
    } 
        public List getMineralByApplicationId(String appId) {
            List<String> mineral = new ArrayList<String>();

        PreparedStatement query =getCon().prepareStatement("select * from  multiple_mineral_report(?) as types(val1 varchar)",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);      

                query.setInt(1, Integer.parseInt(appId));               
                    ResultSet rs=query.executeQuery();

                    String value = new String();
                    i = 0;
                    while(rs.next()){               
                        value = rs.getString(1);
                        mineral.add(value);
                    }

        } 

}

Above the SQL function multiple_mineral_report(?) returns string. When the above code is executed the following exception is thrown:

org.postgresql.util.PSQLException: ERROR: a column definition list is only allowed for functions returning "record"

    CREATE OR REPLACE FUNCTION multiple_mineral_report(b integer)
  RETURNS character varying AS
$BODY$
declare
k varchar := '';    
sql_res record;
begin
 for sql_res in execute 'select  mineral_name from application_mineral as am inner join mineral as m on(am.mineral=m.mineral_id) where am.application_id = '||$1||'' loop
    k = k || sql_res.mineral_name || ', ';
 end loop;
 return k;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION multiple_mineral_report(integer) OWNER TO postgres;

SShould add more information : start with the language you use. — provençal le breton
– provençal le breton, Commented Mar 5, 2013 at 9:42
Please show us the create function statement for multiple_mineral_report — user330315
– user330315, Commented Mar 5, 2013 at 11:36

Erwin Brandstetter · Accepted Answer · 2013-03-06 04:52:42Z

2

Your error is here:

PreparedStatement query =getCon().prepareStatement(
"select * from  multiple_mineral_report(?) as types(val1 varchar)"
,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

You don't have to (and actually cannot) define the return type with a column definition list if it is well defined already. Your function is defined as

RETURNS character varying

Drop the bold part and this particular error should go away.
But really, since your function returns a single value, you want to call your function just like this:

SELECT multiple_mineral_report(?)

edited Mar 6, 2013 at 4:52

answered Mar 6, 2013 at 4:47

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Postgres error: a column definition list is only allowed for functions returning "record"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related