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

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.

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