Alias name from subquery

Hi Jeffrey,

Thanks for your quick response!

Hoover, Jeffrey wrote:

select (SELECT name from colnames WHERE id=1) as entry from entries;

I think, I should have been a bit clearer in what I need:

I've got two tables, colnames and entries:

test=# SELECT * from colnames;
id | name
----+------
1 | col1
(1 row)

test=# SELECT entry from entries;
entry
--------
first
second
third
(3 rows)

I would like to get the column name "entry" replaced by an alias "col1",
just like this:

test=# SELECT entry as col1 from entries;
col1
--------
first
second
third
(3 rows)

_But_, I don't want to give the alias explicitely, instead it should be
taken from a second table 'colnames', i.e. something like the line I
sent in my initial mail. Any idea?

Thanks again,

Felix

BTW, here's what I get from your command:

test=# select (SELECT name from colnames WHERE id=1) as entry from entries;
entry
-------
col1
col1
col1
(3 rows)

On 30/09/2008 14:21, Felix Homann wrote:

I would like to set an alias name for a column from a subquery, i.e.
something like this:

SELECT entry AS (SELECT name from colnames WHERE id=1) from entries ;

select entry from (select name from colnames where id = 1) as
entry_with_different_name;

...maybe?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Raymond O'Donnell wrote:

select entry from (select name from colnames where id = 1) as
entry_with_different_name;

...maybe?

Thanks Ray!

No, "entry_with_different_name" should be the result of "SELECT name
FROM colnames WITH id=1".

Kind regards,

Felix

On Tue, Sep 30, 2008 at 03:21:53PM +0200, Felix Homann wrote:

I would like to set an alias name for a column from a subquery, i.e.
something like this:

SELECT entry AS (SELECT name from colnames WHERE id=1) from entries ;

Obviously it doesn't work _this_ way, but is there _any_ way to do it?

Generate the SQL correctly in the first place? :)

I think you may be trying to solve the wrong problem, what are you
really trying to do?

Sam

On Tue, Sep 30, 2008 at 8:29 AM, Felix Homann <fexpop@onlinehome.de> wrote:

_But_, I don't want to give the alias explicitely, instead it should be
taken from a second table 'colnames', i.e. something like the line I sent in
my initial mail. Any idea?

Then you'll have to build a query in plpgsql and execute it to get
what you want. You can't do what you're trying to do in normal SQL.
At least I don't think it's possible.

From what i know, Aliases are literals, they are not variables, hence they
cannot be derived from something. you can derive them outside the scope of
normal SQL by using functions or shell scripts, but probably not inside an
SQL context.

rgds,
dotyet

On Tue, Sep 30, 2008 at 12:10 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

Hi Sam,

Sam Mason wrote:

I think you may be trying to solve the wrong problem, what are you
really trying to do?

Here you go. I have some tables created like this:

CREATE TABLE player(
id INTEGER PRIMARY KEY,
name varchar(20)
);

CREATE TABLE transfer(
id SERIAL PRIMARY KEY,
fromID INTEGER REFERENCES player(id),
toID INTEGER REFERENCES player(id),
amount numeric
);

Now, let's fill them with some data:

INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
'3', '1'), ('2','1','60');

I would now like to have something like a view that transforms the table
"transfer" from this:

test=# SELECT * from transfer;
id | fromid | toid | amount
----+--------+------+--------
1 | 1 | 2 | 3
2 | 1 | 3 | 1
3 | 2 | 1 | 60

into this:

id | Peter | David | Fritz | ...even more Names from player table
---+-------+-------+-------+-------------------------------------
1 | -3 | 3 | 0 | 0
2 | -1 | 0 | 1 | 0
3 | 60 | -60 | 0 | 0

In other words, I would like to have a named column for every Name in
the player table. I _can_ create such a view manually if I know each
player.name beforehand, but I don't know how to automate it for any
given number of players. (Think of something like a sparse interaction
matrix representation.)

Maybe it's the "wrong" problem I'm trying to solve, but at least I would
like to know whether it's possible or not.

Kind regards,

Felix

On Tue, Sep 30, 2008 at 11:45 AM, Felix Homann <fexpop@onlinehome.de> wrote:

Hi Sam,
In other words, I would like to have a named column for every Name in the
player table. I _can_ create such a view manually if I know each player.name
beforehand, but I don't know how to automate it for any given number of
players. (Think of something like a sparse interaction matrix
representation.)

Ahhh, you might do better with crosstab functions then. look up the
tablefunc contrib module.

I think you should look at crosstab contrib module.

Regards,
Taras Kopets

On 9/30/08, Felix Homann <fexpop@onlinehome.de> wrote:

Hi Sam,

Sam Mason wrote:

I think you may be trying to solve the wrong problem, what are you
really trying to do?

Here you go. I have some tables created like this:

CREATE TABLE player(
id INTEGER PRIMARY KEY,
name varchar(20)
);

CREATE TABLE transfer(
id SERIAL PRIMARY KEY,
fromID INTEGER REFERENCES player(id),
toID INTEGER REFERENCES player(id),
amount numeric
);

Now, let's fill them with some data:

INSERT INTO player VALUES ('1', 'Peter'), ('2','David'), ('3','Fritz');
INSERT INTO transfer(fromID, toID, amount) VALUES ('1','2','3'), ('1',
'3', '1'), ('2','1','60');

I would now like to have something like a view that transforms the table
"transfer" from this:

test=# SELECT * from transfer;
id | fromid | toid | amount
----+--------+------+--------
1 | 1 | 2 | 3
2 | 1 | 3 | 1
3 | 2 | 1 | 60

into this:

id | Peter | David | Fritz | ...even more Names from player table
---+-------+-------+-------+-------------------------------------
1 | -3 | 3 | 0 | 0
2 | -1 | 0 | 1 | 0
3 | 60 | -60 | 0 | 0

In other words, I would like to have a named column for every Name in
the player table. I _can_ create such a view manually if I know each
player.name beforehand, but I don't know how to automate it for any
given number of players. (Think of something like a sparse interaction
matrix representation.)

Maybe it's the "wrong" problem I'm trying to solve, but at least I would
like to know whether it's possible or not.

Kind regards,

Felix

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent from Gmail for mobile | mobile.google.com

Thanks to Scott and Taras for pointing me to the crosstab functions. I
only had a quick look but they seem very helpful!

Kind regards,

Felix