Thread: dynamic 'INSERT' query?
<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">How can we set A1, A2 values in dynamic 'INSERT’ query?</span></font><p class="MsoNormal"><font face="Verdana"size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">DECLARE</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> _record RECORD;</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> _sql VARCHAR(2000);</span></font><p class="MsoNormal"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">FOR _record IN SELECT A1, A2 FROM A</span></font><p class="MsoNormal"><font face="Verdana" size="2"><spanstyle="font-size:10.0pt;font-family:Verdana">LOOP</span></font><font face="Verdana" size="2"><span style="font-size:10.0pt;font-family:Verdana"></span></font><pclass="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal" style="text-indent:.5in"><font face="Verdana" size="2"><span style="font-size:10.0pt;font-family:Verdana">_sql:= 'INSERT INTO B VALUES (<b><font color="red"><span style="color:red;font-weight:bold">:A1,:A2</span></font></b>)’;</span></font><p class="MsoNormal" style="text-indent:.5in"><fontface="Verdana" size="2"><span style="font-size:10.0pt;font-family:Verdana"> </span></font><pclass="MsoNormal" style="text-indent:.5in"><font face="Verdana"size="2"><span style="font-size:10.0pt;font-family:Verdana">EXECUTE (_sql);</span></font><p class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">END LOOP;</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">=================================================================</span></font><p class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">I can do this as (but I need another way instead of using || operator).</span></font><p class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p class="MsoNormal" style="text-indent:.5in"><font face="Verdana" size="2"><span style="font-size:10.0pt;font-family:Verdana">_sql:= 'INSERT INTO B VALUES (‘ || _record.A1 || ’,’ || _record.A2 || ’)’;</span></font><pclass="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana">=================================================================</span></font><p class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; font-family:Verdana"> </span></font><p><font face="Verdana" size="2"><span style="font-size:10.0pt">Regards<br /> DineshPandey</span></font><font face="Times New Roman"><span style="font-family: "Times New Roman""><br /><br /><br /><b><font color="gray"><span style="color:gray;font-weight:bold">----------------------------------------------------------------------------------</span></font></b></span></font><br /><b><fontcolor="gray"><span style="color:gray;font-weight:bold">Dinesh Pandey</span></font></b> <br /><font color="gray"><spanstyle="color:gray">Sr. Software Engineer<br /><br /></span></font><p class="MsoNormal"><font face="TimesNew Roman" size="3"><span style="font-size: 12.0pt"> </span></font></div>
On Apr 14, 2005, at 7:37 AM, Dinesh Pandey wrote: > How can we set A1, A2 values in dynamic 'INSERT’ query? > > > > DECLARE > > _record RECORD; > > _sql VARCHAR(2000); > > > > FOR _record IN SELECT A1, A2 FROM A > > LOOP > > > > _sql := 'INSERT INTO B VALUES (:A1, :A2)’; > > > > EXECUTE (_sql); > > > > END LOOP; > > > > ================================================================= > > I can do this as (but I need another way instead of using || operator). > > > > _sql := 'INSERT INTO B VALUES (‘ || _record.A1 || ’,’ || _record.A2 || > ’)’; > Dinesh, I think what you are showing here IS the way to build up a dynamic sql statement. I'm not sure that you can write a prepared statement within the body of a function, which would then look more like what you are suggesting you want to do--perhaps others on the list can enlighten us about that. In any case, why won't using the || operator work for you? Sean
FOR _record IN SELECT A1, A2 FROM A
LOOP
--[Dinesh Pandey] -- Sorry I didn't get this part.
_sql := "INSERT INTO B VALUES ( '*" || _record.A1 || "'** , '" || _record.A2
|| "' ***)";
EXECUTE (_sql);
END LOOP;
Dinesh Pandey wrote:
> How can we set A1, A2 values in dynamic 'INSERT' query?
>
> DECLARE
>
> _record RECORD;
>
> _sql VARCHAR(2000);
>
> FOR _record IN SELECT A1, A2 FROM A
>
> LOOP
>
> _sql := 'INSERT INTO B VALUES (*:A1, :A2*)';
>
> EXECUTE (_sql);
>
> END LOOP;
>
> =================================================================
>
> I can do this as (but I need another way instead of using || operator).
>
> _sql := 'INSERT INTO B VALUES (' || _record.A1 || ',' || _record.A2 ||
> ')';
>
> =================================================================
>
> Regards
> Dinesh Pandey
>
>
>
*---------------------------------------------------------------------------
-------*
> *Dinesh Pandey*
> Sr. Software Engineer
>
"Dinesh Pandey" <dpandey@secf.com> writes:
> FOR _record IN SELECT A1, A2 FROM A
> LOOP
> _sql := 'INSERT INTO B VALUES (:A1, :A2)';
> EXECUTE (_sql);
Why do you need a dynamic query here at all? You could just do
FOR _record IN SELECT A1, A2 FROM A LOOP INSERT INTO B VALUES (_record.A1, _record.A2);
This would be much faster as well as simpler to code.
As far as I can see offhand, you only need EXECUTE when you want to
change table and/or field names from one execution to the next of
a particular query. If you have such a problem, you are omitting
the important details ...
regards, tom lane
Yes here the TABLE NAME is also coming at runtime. Like In Oracle I have done in this way: ------------------------------------------- LOOPv_sql := 'INSERT INTO ' || mytable || ' VALUES(:in_sentryid_id, :in_node_id)'; EXECUTE IMMEDIATE v_sql USING v_sentryid, v_nodeid; END LOOP; How to convert this into Postgres????? Thanks Dinesh Pandey