Date: Thu, 30 Jul 2020 10:36:00 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Chamath Sajeewa <csgsajeewa@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-novice@lists.postgresql.org
Subject: Re: PGSQL returning into in insert statement
Message-ID: <20200730143600.GX12375@tamriel.snowman.net>
References: 
 <CACpofegcG+Ke2r4yoCpwRKPnV8r+fiGfB1w=ur4M3KHvTFKkpg@mail.gmail.com>
 <1352566.1596078339@sss.pgh.pa.us>
 <CACpofejjgSR_TGVwLb8cBJ=wnuf_g1MH+Aogc3mMNSRwP3C6mg@mail.gmail.com>
 <CACpofeiBnKyFFf4a3_rFGSkx+_18drQVBv6mMkYQo4W5+oi0JA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="j1GQOFr+JpStR0Db"
Content-Disposition: inline
In-Reply-To: 
 <CACpofeiBnKyFFf4a3_rFGSkx+_18drQVBv6mMkYQo4W5+oi0JA@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--j1GQOFr+JpStR0Db
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Chamath Sajeewa (csgsajeewa@gmail.com) wrote:
> Btw oracle support this in plain sql too.

Please don't top-post.

The question here, it would seem, is- where do you expect that
returnvalue to go?  If you want it to be returned to you (making the
INSERT look like a SELECT) then you can just say 'returning value;' and
you don't need to do anything else.

If you want to set a server-side variable with the result, you could do
something like:

=3D*> with myinsert as
  (insert into test_table(key,value) values ('key1',5) returning value)
  select set_config('myvar.value'::text,myinsert.value::text,true)
  from myinsert;
 set_config=20
------------
 5
(1 row)

Which you can then query with:

=3D*> select current_setting('myvar.value');
 current_setting=20
-----------------
 5
(1 row)

Thanks,

Stephen

--j1GQOFr+JpStR0Db
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJfItrQAAoJEO1sijiDR2RVVkUP/2qBzBwm1/ucjsvlNfOgaZ6p
v7zFjBwduvJrMRSESyDvTjhTJIcn0GSykJIO1PEU4oyp0avWs43V/K2kKc0Ep7iI
Tnfk+qxgg1sK99RRqg1s6oXriJCwTMUWK8KlddNHDvGqjd6fFw9zzj8B22XhUe4w
XJi2eVAuxdrz9w4YOkU5lkoHb8I4iklzTjCtCAXOmdgg/HIN1R3+96Ifrxoqtxoz
busz3RsNjzeRAoI+kXgbZ0admoeb3a7572iGLZKZaoeVklqLQ+UU78FlJ0ZyvIBN
rzN9zF/89KVXBIjmgnFD3RBv0yechcXeM0oxwB1qiMFMI7c4QJZFmvF7prJ1KuD1
+MP9QCKMKjHQCi3vSqAfJiBC5Mxr4Fh7Aox/V734wCojVLgjnT9ni+1stzQm0R9n
3ZR2rSn+3qh6LizQoBHuRQQz40DfaotBZa+xZC2R7Hyeyxu+6MpDih51wI2wWPFj
IQJMVlsbkUB+0D4TQKuUDMcDeKbmRAXWhNTFdM/jJA5LzNDpvG9ZM5mp3tJnXX1a
029HjtNopXCEHJOIjhD8lHNjMnfsuNu7fRhGzqCOMzluCr0tqzW4XovNM/uaoyi6
dS+xdQbrRo/IJ5UVUy9ceDQ0yJyLzf11Z8ILaZAVExEJxdUFjkBQg7CibSjJ2PL5
+4wCAUbM08sH/ZN2tmwo
=Tr7q
-----END PGP SIGNATURE-----

--j1GQOFr+JpStR0Db--