Thread: Prepared query ?

Prepared query ?

From

"Dinesh Pandey"

Date:

14 April 2005, 14:31:55

<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">How can I </span></font><ol start="1" style="margin-top:0in" type="1"><li class="MsoNormal"
style="mso-list:l0level1 lfo1"><font face="Verdana" size="2"><span style="font-size:10.0pt;font-family:Verdana">Write a
preparedquery and </span></font><li class="MsoNormal" style="mso-list:l0 level1 lfo1"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt;font-family:Verdana">Set values at run time and </span></font><li
class="MsoNormal"style="mso-list:l0 level1 lfo1"><font face="Verdana" size="2"><span
style="font-size:10.0pt;font-family:Verdana">Executethat query for different values.</span></font></ol><p
class="MsoNormal"style="margin-left:.25in"><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">LOOP</span></font><fontface="Verdana" size="2"><span
style="font-size:10.0pt;font-family:Verdana"></span></font><pclass="MsoNormal" style="margin-left:.25in"><font
face="Verdana"size="2"><span style="font-size:10.0pt;font-family:Verdana">     …..</span></font><p class="MsoNormal"
style="margin-left:.25in"><fontface="Verdana" size="2"><span style="font-size:10.0pt;font-family:Verdana">    
…..</span></font><pclass="MsoNormal" style="margin-left:.25in;text-indent:.25in"><font face="Verdana" size="2"><span
style="font-size:10.0pt;font-family:Verdana">sql:= INSERT INTO MYTABLE VALUES(?, ?);</span></font><p class="MsoNormal"
style="margin-left:.25in;text-indent:.25in"><fontface="Verdana" size="2"><span
style="font-size:10.0pt;font-family:Verdana">……</span></font><pclass="MsoNormal"
style="margin-left:.25in;text-indent:.25in"><fontface="Verdana" size="2"><span
style="font-size:10.0pt;font-family:Verdana">--EXECUTE(sql);</span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="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><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 /> Second Foundation (India) Pvt. Ltd.<br /> Plot#
52<br/> Industrial Area, Phase II<br /> Chandigarh. (India)<br /> PH: (O) 0172-2639202, Extn: 233</span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div>

Re: Prepared query ?

From

Frank Bax

Date:

14 April 2005, 14:54:20

At 10:28 AM 4/14/05, Dinesh Pandey wrote:<br /><blockquote cite="cite" class="cite" type="cite"><font face="verdana"
size="2">Howcan I <ol><li>Write a prepared query and <li>Set values at run time and <li>Execute that query for
differentvalues.</ol></font></blockquote><br /> Depends on what language you are using - try interfaces mailing
list.<br/>

Re: Prepared query ?

From

Richard Huxton

Date:

14 April 2005, 14:58:55

Dinesh Pandey wrote:
> How can I
> 
>    1. Write a prepared query and
>    2. Set values at run time and
>    3. Execute that query for different values.

> LOOP
>      …..
>      …..
> sql := INSERT INTO MYTABLE VALUES(?, ?);

> --EXECUTE (sql);

You don't say what language you want to use, but assuming it's plpgsql, 
then for the simple case you can just do:

LOOP  INSERT INTO mytable VALUES (var1, var2);
END LOOP

Now, let's say you wanted to change mytable as well as the values, you'd use  sql := ''INSERT INTO '' ||
quote_ident(my_table_variable)|| '' 
 
VALUES  ...etc'';  EXECUTE sql;

Does that help?

--   Richard Huxton  Archonet Ltd

Re: Prepared query ?

From

"Dinesh Pandey"

Date:

14 April 2005, 15:01:10

I am using PL/pgSQL.

Thanks

Dinesh Pandey

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Frank Bax
Sent: Thursday, April 14, 2005 8:24 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Prepared query ?

At 10:28 AM 4/14/05, Dinesh Pandey wrote:

How can I

Write a prepared query and
Set values at run time and
Execute that query for different values.

Depends on what language you are using - try interfaces mailing list.

Re: Prepared query ?

From

"Dinesh Pandey"

Date:

14 April 2005, 15:06:29

Yes you got my problem correctly. I want to change tablename and values at
run time.

Is there any other way instead using "||" (append) ? like prepared
statement.

PREPARE fooplan (int, text, bool, numeric) AS   INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);


Thanks
Dinesh Pandey

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com] 
Sent: Thursday, April 14, 2005 8:29 PM
To: dpandey@secf.com
Cc: 'PostgreSQL'
Subject: Re: [SQL] Prepared query ?

Dinesh Pandey wrote:
> How can I
> 
>    1. Write a prepared query and
>    2. Set values at run time and
>    3. Execute that query for different values.

> LOOP
>      ...
>      ...
> sql := INSERT INTO MYTABLE VALUES(?, ?);

> --EXECUTE (sql);

You don't say what language you want to use, but assuming it's plpgsql, 
then for the simple case you can just do:

LOOP  INSERT INTO mytable VALUES (var1, var2);
END LOOP

Now, let's say you wanted to change mytable as well as the values, you'd use  sql := ''INSERT INTO '' ||
quote_ident(my_table_variable)|| '' 
 
VALUES  ...etc'';  EXECUTE sql;

Does that help?

--   Richard Huxton  Archonet Ltd