引起的:org.postgresql.util。PSQLException:错误:在返回时的语法错误

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

我正在使用存储过程来进行所有的db交互

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..

我弄不清出什么事了。

3 个解决方案

#1

Change the WITH part like below

更改如下所示的部分

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

(OR) make it without WITH clause like

(或)不带类似条款

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

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


  RETURN out_inserted_id;

END;
$$ LANGUAGE plpgsql

#2

You basically have two options here:

你基本上有两个选择:

PL/pgSQL

PL / pgSQL

You need to supply a target for the id value:

您需要为id值提供一个目标:

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
  INSERT INTO ezhire.user(name, email, image_url, provider)
  VALUES (in_name, in_email, in_image_url, in_provider)
  RETURNING id INTO out_inserted_id;

  RETURN out_inserted_id;
END;
$$ LANGUAGE plpgsql;

SQL function

SQL函数

Seeing that you only do pure SQL in the function, use a SQL language function which is faster and simpler to write:

既然您只在函数中执行纯SQL，那么使用一个SQL语言函数，它写起来更快、更简单:

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 $$
  INSERT INTO ezhire.user(name, email, image_url, provider)
  VALUES (in_name, in_email, in_image_url, in_provider)
  RETURNING id;
$$ LANGUAGE sql;

#3

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

#1