Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B2D699F9C52
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 27 Nov 2006 22:31:23 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
	with ESMTP id 07858-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 27 Nov 2006 22:31:19 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from web90509.mail.mud.yahoo.com (web90509.mail.mud.yahoo.com
	[216.252.100.176])
	by postgresql.org (Postfix) with SMTP id C458E9FA28E
	for <pgsql-sql@postgresql.org>; Mon, 27 Nov 2006 22:31:17 -0400 (AST)
Received: (qmail 58153 invoked by uid 60001); 28 Nov 2006 02:31:16 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=2paLxS+pKZMFycfFsbZ3VHFBJNOsXli/jTDUhut5kC+2k2pQv/RzQdprsjkxmAqzHVFpJDgEDk61zeJ0+hvvPPUHyiYHnj3H1tuWAPOuwqZ2rKaLuSU3kNWlFVCYiLKjIQp8e3eqalzWoC/EMIv8n+GvqcjSUTWHvwbTZ58QWBQ=
	;
Message-ID: <20061128023116.58151.qmail@web90509.mail.mud.yahoo.com>
Received: from [219.95.251.131] by web90509.mail.mud.yahoo.com via HTTP;
	Mon, 27 Nov 2006 18:31:16 PST
Date: Mon, 27 Nov 2006 18:31:16 -0800 (PST)
From: Mulham freshcode <mulhamcode@yahoo.com>
Subject: Re: select into
To: aklaver@comcast.net, pgsql-sql@postgresql.org
Cc: Mulham freshcode <mulhamcode@yahoo.com>
In-Reply-To: <200611261823.44994.aklaver@comcast.net>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1249338842-1164681076=:58075"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.18 tagged_above=0 required=5 tests=AWL, BAYES_00,
	DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE
X-Spam-Level: *
X-Archive-Number: 200611/268
X-Sequence-Number: 26920

--0-1249338842-1164681076=:58075
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi Adrian,

Thanks very much for your help...it is a pity you can't do this in plpgsql coz i have almost every thing else I need, and I hate to use yet another language. What does it take to add this mechanism to the language? Is any one planning to add it? It can come it handy I bet.

Thanks again,

Mustafa...



Adrian Klaver <aklaver@comcast.net> wrote: On Sunday 26 November 2006 02:45 pm, Adrian Klaver wrote:

>
> I am afraid I can't make it work either.
I could not make it work with pl/pgsql, but I did manage to come up with a 
solution using pl/pythonu.
The function is as follows-

CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS
$Body$
tbl_name=args[0]
cols=plpy.prepare("select column_name from information_schema.columns where\
table_name=$1",["text"])
clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])
clean_tbl=plpy.execute(clean,[tbl_name])
ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])
data_rs=plpy.execute('select * from '+tbl_name)
cols_rs=plpy.execute(cols,[tbl_name])
for i in range(len(data_rs)):
    for j in range(len(cols_rs)):
        plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],
        data_rs[i][cols_rs[j]['column_name'] ]))
$Body$
LANGUAGE plpythonu;

For this to work I created a table dat_col(table_name text,column_name 
text,column_data text). The function deletes old data from the table before 
it is run, based on table name. Just run as dat_col_py("table name"). This 
assumes you have pl/pythonu installed.
-- 
Adrian Klaver 
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


 
---------------------------------
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
--0-1249338842-1164681076=:58075
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi Adrian,<br><br>Thanks very much for your help...it is a pity you can't do this in plpgsql coz i have almost every thing else I need, and I hate to use yet another language. What does it take to add this mechanism to the language? Is any one planning to add it? It can come it handy I bet.<br><br>Thanks again,<br><br>Mustafa...<br><br><br><br><b><i>Adrian Klaver &lt;aklaver@comcast.net&gt;</i></b> wrote:<blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> On Sunday 26 November 2006 02:45 pm, Adrian Klaver wrote:<br><br>&gt;<br>&gt; I am afraid I can't make it work either.<br>I could not make it work with pl/pgsql, but I did manage to come up with a <br>solution using pl/pythonu.<br>The function is as follows-<br><br>CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS<br>$Body$<br>tbl_name=args[0]<br>cols=plpy.prepare("select column_name from information_schema.columns
 where\<br>table_name=$1",["text"])<br>clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])<br>clean_tbl=plpy.execute(clean,[tbl_name])<br>ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])<br>data_rs=plpy.execute('select * from '+tbl_name)<br>cols_rs=plpy.execute(cols,[tbl_name])<br>for i in range(len(data_rs)):<br>    for j in range(len(cols_rs)):<br>        plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],<br>        data_rs[i][cols_rs[j]['column_name'] ]))<br>$Body$<br>LANGUAGE plpythonu;<br><br>For this to work I created a table dat_col(table_name text,column_name <br>text,column_data text). The function deletes old data from the table before <br>it is run, based on table name. Just run as dat_col_py("table name"). This <br>assumes you have pl/pythonu installed.<br>-- <br>Adrian Klaver <br>aklaver@comcast.net<br><br>---------------------------(end of broadcast)---------------------------<br>TIP 9: In versions below 8.0,
 the planner will ignore your desire to<br>       choose an index scan if your joining column's datatypes do not<br>       match<br></blockquote><br><p>&#32;
<hr size=1>Cheap Talk? <a href="http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com">Check out</a> Yahoo! Messenger's low PC-to-Phone call rates.
--0-1249338842-1164681076=:58075--