Postgres Pro
Downloads
Home   >   mailing lists

Thread: output inserted

output inserted

From
Peter Kroon
Date:
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;

Re: output inserted

From
Ryan Kelly
Date:
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

Re: output inserted

From
"David Johnston"
Date:

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.