From: Adrian Klaver <aklaver@comcast.net>
Reply-To: aklaver@comcast.net
To: pgsql-sql@postgresql.org
Subject: Re: select into
Date: Sun, 26 Nov 2006 18:23:44 -0800
User-Agent: KMail/1.6.2
Cc: Mulham freshcode <mulhamcode@yahoo.com>
References: <20061125041743.60657.qmail@web90513.mail.mud.yahoo.com>
	<200611261445.57554.aklaver@comcast.net>
In-Reply-To: <200611261445.57554.aklaver@comcast.net>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <200611261823.44994.aklaver@comcast.net>

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