your problem is a little unorthodox, but i will spare you the "why the heck do you want to do this?" discussion and assume you have good reasons... so here's a "dynamic SQL" approach:
select 'create table test (id bigint, '|| array_to_string(array(select a||' text' from foo),', ')||');'; not pretty, but you get the idea. > -----Original Message----- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Marco Lechner > Sent: Monday, December 15, 2008 1:42 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] create table with rownames as values in column of > seciond table > > Hi list, > > I try to create a table using plpgsql or plpythonu. I'm starting with > programming in postgresql therfore I'm a little bit confused. I know a > little bit of python and a little bit of SQL. May be some hints could > help me gettin into it: > > My problem is: > I'm having a table with a column a and certain values in it. > _a_|_b_ > ab | v1 > de | v2 > fc | v3 > wd | v4 > ed | v5 > ...|... > > And I need a script to make the first column the rownames and the other > columns the values of the newly created table: > > CREATE TABLE test( > ID BIGINT, > ab TEXT, > de TEXT, > fc TEXT, > wd TEXT, > ed TEXT, > ... > PRIMARY KEY(ID)); > > and: INSERT INTO test VALUES('v1', 'v2', 'v3', 'v4', 'v5', ...); > > The number of rows and the names of the columns vary. > > I get a list of the values in the first table by SELECT a FROM table1; > But how to put this into a CREATE TABLE script - and fill in the other > columns as values? > > Marco -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
