pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddynamic 'INSERT' query?
5+ messages / 3 participants
[nested] [flat]
* dynamic 'INSERT' query?
@ 2005-04-14 11:37 Dinesh Pandey <dpandey@secf.com>
0 siblings, 2 replies; 5+ messages in thread
From: Dinesh Pandey @ 2005-04-14 11:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic 'INSERT' query?
@ 2005-04-14 11:59 Sean Davis <sdavis2@mail.nih.gov>
parent: Dinesh Pandey <dpandey@secf.com>
1 sibling, 0 replies; 5+ messages in thread
From: Sean Davis @ 2005-04-14 11:59 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic 'INSERT' query?
@ 2005-04-14 12:03 Dinesh Pandey <dpandey@secf.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dinesh Pandey @ 2005-04-14 12:03 UTC (permalink / raw)
To: 'Juris Zeltins' <juris.zeltins@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic 'INSERT' query?
@ 2005-04-14 14:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dinesh Pandey <dpandey@secf.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-04-14 14:53 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: dynamic 'INSERT' query?
@ 2005-04-14 14:57 Dinesh Pandey <dpandey@secf.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Dinesh Pandey @ 2005-04-14 14:57 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Yes here the TABLE NAME is also coming at runtime.
Like In Oracle I have done in this way:
-------------------------------------------
LOOP
v_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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-14 14:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-14 11:37 dynamic 'INSERT' query? Dinesh Pandey <dpandey@secf.com>
2005-04-14 11:59 ` Sean Davis <sdavis2@mail.nih.gov>
2005-04-14 14:53 ` Re: dynamic 'INSERT' query? Tom Lane <tgl@sss.pgh.pa.us>
2005-04-14 14:57 ` Re: dynamic 'INSERT' query? Dinesh Pandey <dpandey@secf.com>
2005-04-14 12:03 Re: dynamic 'INSERT' query? Dinesh Pandey <dpandey@secf.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox