output inserted

On Wed, Nov 21, 2012 at 02:41:24PM +0100, Peter Kroon wrote:

How to I output the insert in PostgreSQL?

DROP TABLE IF EXISTS a_001;
CREATE TEMP TABLE a_001(
vl text
);
DROP TABLE IF EXISTS a_002;
CREATE TEMP TABLE a_002(
vl text
);

INSERT INTO a_001
OUTPUT INSERTED.* INTO a_002 --mssql
SELECT 'text for insertion';

SELECT vl FROM a_002;

WITH data AS (
INSERT INTO a_001
SELECT 'text for insertion'
RETURNING *
)
INSERT INTO a_002
SELECT * FROM data;

This is one possible solution.

-Ryan P. Kelly

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Peter Kroon
Sent: Wednesday, November 21, 2012 8:41 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] output inserted

How to I output the insert in PostgreSQL?

DROP TABLE IF EXISTS a_001;

CREATE TEMP TABLE a_001(

vl text

);

DROP TABLE IF EXISTS a_002;

CREATE TEMP TABLE a_002(

vl text

);

INSERT INTO a_001

OUTPUT INSERTED.* INTO a_002 --mssql

SELECT 'text for insertion';

SELECT vl FROM a_002;

My best guess, since I can only infer what that particular syntax means, is
that you have to put an AFTER INSERT TRIGGER on table a_001.

David J.