How to convert "output deleted/inserted into" in MySQL to Postgres

On 2/20/2015 12:59 PM, Tong Michael wrote:

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.

no idea what 'deleted' means, but, assuming this in is plpgsql, use
UPDATE .... RETURNING ...INTO rather than OUTPUT INTO,

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

now, if this in a trigger function, you can refer to OLD. and NEW.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

hey, john, i did as you said like:
update db.user
set deleted = 1,
updateterminal = UpdateTerminal,
updateuser = UpdateUser,
updatedate = UpdateDate
returning
credittypeid,
creditid,
amount
into ReconDeleted
where deleted = 0
and clientid = ClientID
);

I have ERROR: syntax error at or near "into"

ReconDeleted has three columns
CreditTypeID bigint,
CreditID bigint,
Amount money

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/20/2015 01:41 PM, Michael_LT wrote:

hey, john, i did as you said like:
update db.user
set deleted = 1,
updateterminal = UpdateTerminal,
updateuser = UpdateUser,
updatedate = UpdateDate
returning
credittypeid,
creditid,
amount
into ReconDeleted
where deleted = 0
and clientid = ClientID
);

I have ERROR: syntax error at or near "into"

ReconDeleted has three columns
CreditTypeID bigint,
CreditID bigint,
Amount money

To have this make any sense to the rest of us, you will need to show
your complete function.

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Michael,

hey, john, i did as you said like:
update db.user
set deleted = 1,
updateterminal = UpdateTerminal,
updateuser = UpdateUser,
updatedate = UpdateDate
returning
credittypeid,
creditid,
amount
into ReconDeleted
where deleted = 0
and clientid = ClientID
);

I have ERROR: syntax error at or near "into"

I think what you need here is a Postgres CTE, because you need to
separate the UPDATE from the INSERT. You can do your query like this:

WITH changes AS (
update db.user
set deleted = 1,
updateterminal = UpdateTerminal,
updateuser = UpdateUser,
updatedate = UpdateDate
returning
credittypeid,
creditid,
amount
)
INSERT INTO ReconDeleted
SELECT * FROM changes
;

(not tested, but see CTE docs if you have troubles)

Paul

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tong Michael wrote on 20.02.2015 21:59:

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?

That is not valid for MySQL it does not have an "OUTPUT DELETED" option for any DML statement.
Plus: MySQL does not have table variables.

The looks much more like SQL Server/T-SQL.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general