Thread: 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

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-owner@postgresql.org [mailto:pgsql-sql-
> owner@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