Обсуждение: how to convert "output deleted/inserted into" in My SQL to Postgres

Поиск
Список
Период
Сортировка

how to convert "output deleted/inserted into" in My SQL to Postgres

От
Michael_LT
Дата:
hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to
convert some stored procedures in MySQL to Postgres and I came across an
issue here that I can't solve:

update  db.user
             set Deleted        = 1
            , UpdateTerminal = @UpdateTerminal
             , UpdateUser     = @UpdateUser
             , UpdateDate     = @UpdateDate
        output deleted.CreditTypeID
             , deleted.CreditID
             , deleted.Amount
          into @ReconDeleted
         where Deleted = 0
           and ClientID = @ClientID;

I think it tried to update a couple of columns in table user and insert
values in other 3 columns into another table ReconDeleted at the same time.
I have issues converting "OUTPUT INTO" and "deleted" items, with my limited
knowledge about Postgres, I don't think we have those in pg. Can someone
tell me how to convert it? Thanks.

PS: it's my first time using this form, nice to meet you guys:)



--
View this message in context:
http://postgresql.nabble.com/how-to-convert-output-deleted-inserted-into-in-My-SQL-to-Postgres-tp5838750.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: how to convert "output deleted/inserted into" in My SQL to Postgres

От
Marc Mamin
Дата:
>hey guys, I'm kinda new to Postgres and I'm learning it now. I have work to
>convert some stored procedures in MySQL to Postgres and I came across an
>issue here that I can't solve:
>
>update  db.user
>             set Deleted        = 1
>            , UpdateTerminal = @UpdateTerminal
>             , UpdateUser     = @UpdateUser
>             , UpdateDate     = @UpdateDate
>        output deleted.CreditTypeID
>             , deleted.CreditID
>             , deleted.Amount
>          into @ReconDeleted
>         where Deleted = 0
>           and ClientID = @ClientID;
>
>I think it tried to update a couple of columns in table user and insert
>values in other 3 columns into another table ReconDeleted at the same time.
>I have issues converting "OUTPUT INTO" and "deleted" items, with my limited
>knowledge about Postgres, I don't think we have those in pg. Can someone
>tell me how to convert it? Thanks.

Hi,

it seems that you are looking for UPDATE RETURNING:

http://www.postgresql.org/docs/9.4/static/sql-update.html

regards,

Marc Mamin