X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 6C2C49DC99A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Feb 2006 04:49:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 09097-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Feb 2006 04:49:52 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.173])
	by postgresql.org (Postfix) with ESMTP id 46F1A9DC81D
	for <pgsql-sql@postgresql.org>; Thu,  2 Feb 2006 04:49:47 -0400 (AST)
Received: from [212.227.126.202] (helo=mrvnet.kundenserver.de)
	by moutng.kundenserver.de with esmtp (Exim 3.35 #1)
	id 1F4a9u-0003Lx-00; Thu, 02 Feb 2006 09:49:42 +0100
Received: from [172.23.4.153] (helo=pustefix153.kundenserver.de)
	by mrvnet.kundenserver.de with esmtp (Exim 3.35 #1)
	id 1F4a9u-00051g-00; Thu, 02 Feb 2006 09:49:42 +0100
Message-Id: <20393697.175191138870182592.JavaMail.servlet@kundenserver>
From: christian.michels@eifelgeist.com
To: <sszabo@megazone.bigpanda.com>
Subject: Re: executing dynamic commands
Cc: <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-Binford: 6100 (more power)
X-Mailer: Webmail
X-Originating-From: 28358181
X-Routing: DE
X-Message-Id: 
 <28358181$1138870182592172.23.4.1532674838@pustefix153.kundenserver.de-1015495427>
X-Received: from pustefix153.kundenserver.de by 212.232.252.130 with HTTP id
	28358181 for [sszabo@megazone.bigpanda.com];
	Thu, 2 Feb 2006 09:49:42 CET
Date: Thu, 02 Feb 2006 09:49:42 +0100
X-Provags-ID: kundenserver.de abuse@kundenserver.de ident:@172.23.4.153
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.67 required=5 tests=[AWL=0.120, NO_REAL_NAME=0.55]
X-Spam-Score: 0.67
X-Spam-Level: 
X-Archive-Number: 200602/18
X-Sequence-Number: 24028

Thanx a lot guys -  it works !

Cheers

Chris


On Wed, 1 Feb 2006 christian ( dot ) michels ( at ) eifelgeist ( dot ) com wrote:

> Hi,
>
> I user PostgreSQl 8.0.4 on Win2003 Server and write a function to copy rows from one table into another table with the same column definition.

> My first approach was to use something like:
>
> query_value :=  'INSERT INTO ' || tabledest || ' SELECT * FROM ' || tablesrc;
> EXECUTE query_value;
>
> This only works if the column definition AND the order between source and destination is the same !
> In my case I have always the same column definitions but they are not in> the same order between source and destination table.

> What I tryed then is to loop through the column definition of the source
> and query the sourcetable for the value. For that I have to execut a
> query with dynamic tablename and dynamic columname to generate two
> stings one with the columndefinitin and one with the columnvalues to
> exececute something like: INSERT INTO tabelfoo (columndefinitinstring)
> VALUES (columnvaluesstring)

You might have better luck with a INSERT ... SELECT where you've reordered
the columns in the select list

INSERT INTO tabledest SELECT <reordered columns to match dest order> FROM
tablesrc