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 [EMAIL PROTECTED] ---------------------------(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