[GENERAL] Alias name from subquery
Hi, 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? 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
Re: [GENERAL] 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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
On Tue, Sep 30, 2008 at 8:29 AM, Felix Homann [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
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 [EMAIL PROTECTED]wrote: On Tue, Sep 30, 2008 at 8:29 AM, Felix Homann [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
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
Re: [GENERAL] Alias name from subquery
On Tue, Sep 30, 2008 at 11:45 AM, Felix Homann [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
I think you should look at crosstab contrib module.
Regards,
Taras Kopets
On 9/30/08, Felix Homann [EMAIL PROTECTED] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alias name from subquery
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
