Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fake table name?

Fake table name?

From
"Roderick A. Anderson"
Date:
I know I've seen this but can't even begin to guess where that was so I'll
ask.  I need to add a fake entry to a UNION.  A row that doesn't exist in
the table and shouldn't.  It's for a CGI script where I want the user have
the all/none/whatever option.

    select tbl_key, equipname from equipment
    UNION
    select 'All', 'All Equipment' from ???

   Heck I can't even remember what is was called in Oracle.


TIA,
Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814

Re: Fake table name?

From
Tom Lane
Date:
"Roderick A. Anderson" <raanders@altoplanos.net> writes:
>     select tbl_key, equipname from equipment
>     UNION
>     select 'All', 'All Equipment' from ???

Postgres has a cleaner answer than a fake table; just omit the
FROM clause:

regression=# create table equipment(tbl_key text, equipname text);
CREATE
regression=# select tbl_key, equipname from equipment
regression-# UNION
regression-# select 'All', 'All Equipment';
 tbl_key |   equipname
---------+---------------
 All     | All Equipment
(1 row)

In some cases you might need to explicitly assign a datatype to the
literals, eg 'All'::text, but in the above example it should work
without that.

            regards, tom lane

Re: Fake table name?

From
"Roderick A. Anderson"
Date:
On Tue, 3 Oct 2000, Tom Lane wrote:

> "Roderick A. Anderson" <raanders@altoplanos.net> writes:
> >     select tbl_key, equipname from equipment
> >     UNION
> >     select 'All', 'All Equipment' from ???
>
> Postgres has a cleaner answer than a fake table; just omit the
> FROM clause:

As I discovered by RTFM.

Thanks for the confirmation.


Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814

Re: Fake table name?

From
tolik@aaanet.ru (Anatoly K. Lasareff)
Date:
>>>>> "RAA" == Roderick A Anderson <raanders@altoplanos.net> writes:

 RAA> I know I've seen this but can't even begin to guess where that was so I'll
 RAA> ask.  I need to add a fake entry to a UNION.  A row that doesn't exist in
 RAA> the table and shouldn't.  It's for a CGI script where I want the user have
 RAA> the all/none/whatever option.

 RAA> select tbl_key, equipname from equipment
 RAA> UNION
 RAA> select 'All', 'All Equipment' from ???

 RAA> Heck I can't even remember what is was called in Oracle.

I guess answer is (assume tbl_key is varchar(20), and equipname is text type):

select tbl_key, equipname from equipment
 UNION
select 'All'::varchar as tbl_key , 'All Equipment'::text as equipname;

You don't need 'from' clause in PostgreSQL in this case.

--
Anatoly K. Lasareff              Email:       tolik@aaanet.ru