Re: wildcard alias

On 11/10/06, Matthew Terenzio <matt(at)jobsforge(dot)com> wrote:
>
> I suppose I should have named things differently but is there a way to
> join two tables with a bunch of identical column names and rather than
> explicitly alias each column just use some sort of wildcard like:
>
> SELECT tablename.* AS alias.*

Well:
qnex=# CREATE TABLE tab1 (a int, b int);
qnex=# CREATE TABLE tab2 (a int, b int);
qnex=# INSERT INTO tab1 VALUES(1,2);
qnex=# INSERT INTO tab1 VALUES(3,4);
qnex=# INSERT INTO tab2 VALUES(1,7);
qnex=# INSERT INTO tab2 VALUES(3,12);

And you want to, instread of:

qnex=# SELECT * FROM tab1 JOIN tab2 USING(a);
a | b | b
---+---+----
1 | 2 | 7
3 | 4 | 12

Do something like:

qnex=# SELECT tab1.*, alias.* FROM tab1 JOIN tab2 alias USING(a);
a | b | a | b
---+---+---+----
1 | 2 | 1 | 7
3 | 4 | 3 | 12

...it is possible, certainly, but I guess you want to rather have
different column names. Then aliasing table names doesn't
change column names, "tab2.b" will be "b" just as well as "alias.b"
will be column labeled "b".

If you want to make a quick&ugly trick, do something like:

qnex=# CREATE VIEW tab2_renamed AS SELECT a AS tab2_a, b AS tab2_b FROM tab2;
qnex=# SELECT * FROM tab1 JOIN tab2_renamed ON (a=tab2_a);
a | b | tab2_a | tab2_b
---+---+--------+--------
1 | 2 | 1 | 7
3 | 4 | 3 | 12

Other than that, I don't see too many options. Also, consider using * in
queries as a bad coding style, and try to avoid it.

Regards,
Dawid