Обсуждение: 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