sql - 如何在 PostgreSql 中插入一条记录并返回新的 id 作为输出参数

Question

我正在尝试插入一条新记录，同时使用以下 sql 将新 ID 作为输出参数值返回:

Insert into ERRORS  ( ErrorId, Date, ErrorInfo )  values  ( :ItemDate, :Text )  returning ErrorId into :ID

我收到以下错误:

Npgsql.NpgsqlException occurred
  BaseMessage=syntax error at or near "into"
  Code=42601
  ColumnName=""
  ConstraintName=""
  DataTypeName=""
  Detail=""
  ErrorCode=-2147467259
  ErrorSql=Insert into ERRORS  ( ErrorId, Date, ErrorInfo )  values  ( (('2014-04-02 08:16:36.045969')::timestamp), (('Test 333 4/2/2014 8:16:36 AM')::text) )  returning ErrorId into 
  File=src\backend\parser\scan.l
  Hint=""
  HResult=-2147467259
  Line=1053
  Message=ERROR: 42601: syntax error at or near "into"
  Position=168
  Routine=scanner_yyerror
  SchemaName=""
  Severity=ERROR
  Source=Npgsql
  TableName=""
  Where=""
  StackTrace:
       at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__9.MoveNext()
       at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
       at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
       at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
       at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
       at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
       at Npgsql.NpgsqlCommand.ExecuteNonQuery()
       at FrozenElephant.Symbiotic.ObjectWriter.Create(IDatabaseTypesFactory factory, Object value, IDbTransaction transaction, Boolean performCommitClose) in E:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\ObjectWriter.vb:line 423
  InnerException: 

我也尝试删除最后一个“into”，但也不起作用。

Answer 1

你必须使用带有 npgsql 的 ExecuteReader 而不是使用 out 参数:

NpgsqlCommand cmd = new NpgsqlCommand(@"Insert into ERRORS(Date, ErrorInfo) values(:ItemDate, :Text) returning ErrorId", conn);

...Add your inpout parameters...

NpgsqlDataReader reader = cmd.ExecuteReader();

int errorId;

while (reader.Read())
{
    errorId = reader.GetInt32(0));
}