Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k12Ue-0002DL-Rr for pgsql-novice@arkaria.postgresql.org; Thu, 30 Jul 2020 06:56:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k12Ud-0000Ut-LM for pgsql-novice@arkaria.postgresql.org; Thu, 30 Jul 2020 06:56:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k12Ud-0000T2-D1 for pgsql-novice@lists.postgresql.org; Thu, 30 Jul 2020 06:56:43 +0000 Received: from mail-il1-x144.google.com ([2607:f8b0:4864:20::144]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1k12Ua-0008WO-V5 for pgsql-novice@lists.postgresql.org; Thu, 30 Jul 2020 06:56:42 +0000 Received: by mail-il1-x144.google.com with SMTP id r12so21585295ilh.4 for ; Wed, 29 Jul 2020 23:56:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=ixJ5JGBw2j1vfF92jH+iX22GRKvDL/TCjZulnpDUjgk=; b=pxa9TKISyxjXtGkqU5EWfh+Clm8jshvjmk6uMen2vSAs4mMlGwOuSh/9VifyMVljEw 4buS/UEFuAo/JwI39hFUvhl8DLsqyvyZwjgnqd72GootnxzvFQ+uhXKtjuAQ1PfBqzhQ QlDH4rgB2mwYUwnFKwRaF9dWBry4OneVGnFy33VLsJDGIl+zorDmTDwqBNpDYlTXuWBL YFWlxxB5TCnzgEIrAqzEy3p0wmHrZhOpKCtiaVdhF8L5THoQEKcQT9dhEU6Xit+mo9uH hqjPspEwmwTicL2BKGkvwJKK8PrdAVi8CDqjgudBwicByVeC85eMLldrGCbxzaKoqZv+ z+yg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=ixJ5JGBw2j1vfF92jH+iX22GRKvDL/TCjZulnpDUjgk=; b=f2kQHQ32BJF8hUMtH1+CBP6QRqKVz5LPdDRJGDLueqHiIUKQ+IKW+8+3bN3pE4MknC 7gKGeJBhSeIpTO30Yg2meYIzzCSjvr0OXz4rb3/qVfhgmyIPFMA/L8X5bnGOZ91eQ1sf y0GtIteCXfs3/eXEWFZAgW2sej+p3KmIkNevxVs47+hb++oOO5zcgLhSyQptN56TVugT iPlz3rt+KTRdSV2kiU0EgYF5s7JSKimqTA1a2b/BHRhTbZ/NDWMzffr+uZjN4aIDbIAV eel7uePwBoEtpR4LKu7UlV277DA3luYwdej8ymlmuy656vAcbvU4dizVm9JjAnZoRofp Zc7g== X-Gm-Message-State: AOAM530iAN0LBD5g8oQ86m7bo/dF5wBXQwFuEfTt+nAKfCRUtE5I8mL+ Fw7mEdUOm5V35BhqCyV30L+S17NF06QHfPj7efox+6lD X-Google-Smtp-Source: ABdhPJymW/D4t4ygVGykvGpT1gsTHN1Xm1E69gMQha0gG/ozQIzH9RXSNPirZ1E8X6tqBlmSoJC0Ff+olxYtNcGNwUI= X-Received: by 2002:a92:5a4c:: with SMTP id o73mr38887285ilb.45.1596092199064; Wed, 29 Jul 2020 23:56:39 -0700 (PDT) MIME-Version: 1.0 References: <1352566.1596078339@sss.pgh.pa.us> In-Reply-To: From: Chamath Sajeewa Date: Thu, 30 Jul 2020 12:26:26 +0530 Message-ID: Subject: Re: PGSQL returning into in insert statement To: Tom Lane Cc: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000008dddda05aba32cf2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000008dddda05aba32cf2 Content-Type: text/plain; charset="UTF-8" Btw oracle support this in plain sql too. On Thu, 30 Jul 2020, 11:42 Chamath Sajeewa, wrote: > Understood. Thank you! > > On Thu, 30 Jul 2020, 08:35 Tom Lane, wrote: > >> Chamath Sajeewa writes: >> > I want to use "returning into" in insert statement. I tried below, >> >> > insert into test_table(key,value) values('key1',5) returning value into >> > returnvalue; >> >> > and below error occurred- syntax error at or near "into". >> >> That is perfectly legit syntax within a plpgsql function, but it >> would produce the mentioned syntax error if you tried to use it >> directly at the SQL level. So I conclude that you're fuzzy about >> the difference between SQL and plpgsql. You can use plpgsql >> statements within plpgsql-language function definitions, and >> within DO blocks. But not as plain SQL. (Plain SQL has no concept >> of variables either, so "into returnvalue" makes no sense there >> anyway.) >> >> regards, tom lane >> > --0000000000008dddda05aba32cf2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Btw oracle support this in plain sql too.

On Thu, 30 Jul 2= 020, 11:42 Chamath Sajeewa, <csg= sajeewa@gmail.com> wrote:
Understood. Thank you!

=
On Thu, 30 Jul 2020, 08:35 Tom Lane, = <tgl@sss.pgh.pa.us> wrote:
Chamath Sajeewa <csgsajeewa@gmail.com> writes:
> I want to use "returning into" in insert statement. I tried = below,

> insert into test_table(key,value) values('key1',5) returning v= alue into
> returnvalue;

> and below error occurred- syntax error at or near "into".
That is perfectly legit syntax within a plpgsql function, but it
would produce the mentioned syntax error if you tried to use it
directly at the SQL level.=C2=A0 So I conclude that you're fuzzy about<= br> the difference between SQL and plpgsql.=C2=A0 You can use plpgsql
statements within plpgsql-language function definitions, and
within DO blocks.=C2=A0 But not as plain SQL.=C2=A0 (Plain SQL has no conce= pt
of variables either, so "into returnvalue" makes no sense there anyway.)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane
--0000000000008dddda05aba32cf2--