CODEWITHSUNDEEP
javasqlpostgresqlplpgsql
Pramil K Prince
Pramil K Prince

Reputation: 113

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

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;

Upvotes: 2

Views: 2538

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659327

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(?)

Upvotes: 2

Related Questions