Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"

Question

This is the error stack when i try to insert a user details into a table

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
  Position: 52
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:618)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:468)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:414)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
    at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:903)
    at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:900)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)

I am using stored procedures to make all my db interation

CREATE OR REPLACE FUNCTION ezhire."insert_user"
(
  in_name character varying, 
  in_email character varying,
  in_image_url character varying, 
  in_provider character varying
) 
RETURNS integer AS $$
DECLARE
  out_inserted_id integer;
BEGIN

  WITH rows as (
    INSERT INTO ezhire.user(name, email, image_url, provider)
    VALUES(in_name, in_email, in_image_url, in_provider)
    RETURNING id
  )
  SELECT id INTO out_inserted_id FROM rows;

  RETURN out_inserted_id;

END;
$$ LANGUAGE plpgsql

I am unable to figure out what is going wrong..

Answer 1

The problem was in my spring code:

Need to change this code

    public Role createRole(final Role role){
      KeyHolder holder = new GeneratedKeyHolder();
      PreparedStatementCreator psc = new PreparedStatementCreator() {
    
        @Override
        public PreparedStatement createPreparedStatement(
            java.sql.Connection conn) throws SQLException {
          PreparedStatement pstmt = conn.prepareStatement(
              QUERIES.ROLE.INSERT_ROLE, new String[] { "id" });
          
          int i = 1;
          pstmt.setString(i++, role.getName());
          pstmt.setString(i++, role.getAbbreviation());
          pstmt.setString(i++, role.getInfo());
          return pstmt;
        }
      };
      logger.info(psc.toString());
      jdbcTemplate.update(psc, holder);
      Integer userId = holder.getKey().intValue();
      role.setId(userId);
      return role;  
    }

To this:

    public Role createRole(final Role role) {
            PreparedStatementCreator psc = new PreparedStatementCreator() {
    
                @Override
                public PreparedStatement createPreparedStatement(Connection conn)
                        throws SQLException {
                    PreparedStatement pstmt = conn
                            .prepareStatement(QUERIES.ROLE.INSERT_ROLE);
                    int i = 1;
                    pstmt.setString(i++, role.getName());
                    pstmt.setString(i++, role.getAbbreviation());
                    pstmt.setString(i++, role.getInfo());
                    logger.debug(pstmt.toString());
                    return pstmt;
                }
            };
            Integer roleId = jdbcTemplate.query(psc,
                    new ResultSetExtractor<Integer>() {
                        @Override
                        public Integer extractData(ResultSet rs)
                                throws SQLException, DataAccessException {
                            if (rs.next()) {
                                return rs.getInt("insert_role");
                            }
                            return null;
                        }
                    });
            role.setId(roleId);
            return role;
        }