Postgres Pro
Downloads
Home   >   mailing lists

Thread: Constructing colum name as alias

Constructing colum name as alias

From
aditya desai
Date:
Hi,
I want to construct column name alias by using different queries. E.g. below.

postgres=# Select ('Status as on '||date_part('day', (SELECT current_timestamp))||'th '||TO_CHAR(current_timestamp, 'Mon'))
postgres-# ;
       ?column?
-----------------------
 Status as on 11th Nov

" Status as on 11th Nov" should be my column name in a query.

SELECT JoiningDate as "Status as on 11th Nov" from empdetails;

How can I achieve this?

Regards,
Aditya.

Re: Constructing colum name as alias

From
"Torsten Grust"
Date:
Hi,

since column names (or table names) are not first-class values in SQL, computing such names at query runtime is impossible.

Best wishes,  
   —Torsten

On Thu, Nov 11, 2021, at 08:46, aditya desai wrote:
Hi,
I want to construct column name alias by using different queries. E.g. below.

postgres=# Select ('Status as on '||date_part('day', (SELECT current_timestamp))||'th '||TO_CHAR(current_timestamp, 'Mon'))
postgres-# ;
       ?column?
-----------------------
 Status as on 11th Nov

" Status as on 11th Nov" should be my column name in a query.

SELECT JoiningDate as "Status as on 11th Nov" from empdetails;

How can I achieve this?

Regards,
Aditya.


--
| Torsten Grust
| teggy@fastmail.com


Re: Constructing colum name as alias

From
"David G. Johnston"
Date:
On Thursday, November 11, 2021, aditya desai <admad123@gmail.com> wrote:

" Status as on 11th Nov" should be my column name in a query.

SELECT JoiningDate as "Status as on 11th Nov" from empdetails;

How can I achieve this?

Dynamic SQL and two independent queries.  One to get the column name.  Then a dynamic one  (say using pl/pgsql) that uses that name.

David J.

Re: Constructing colum name as alias

From
"Theodore M Rolle, Jr."
Date:
May we have an example?

On Thu, Nov 11, 2021, 08:19 David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, November 11, 2021, aditya desai <admad123@gmail.com> wrote:

" Status as on 11th Nov" should be my column name in a query.

SELECT JoiningDate as "Status as on 11th Nov" from empdetails;

How can I achieve this?

Dynamic SQL and two independent queries.  One to get the column name.  Then a dynamic one  (say using pl/pgsql) that uses that name.

David J.

Re: Constructing colum name as alias

From
"David G. Johnston"
Date:
On Thu, Nov 11, 2021 at 6:23 AM Theodore M Rolle, Jr. <stercor@gmail.com> wrote:
May we have an example?

Here are the docs detailing writing dynamic SQL commands in pl/pgsql.

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

David J.

Re: Constructing colum name as alias

From
"David G. Johnston"
Date:
On Thu, Nov 11, 2021 at 7:14 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Nov 11, 2021 at 6:23 AM Theodore M Rolle, Jr. <stercor@gmail.com> wrote:
May we have an example?

Here are the docs detailing writing dynamic SQL commands in pl/pgsql.



Though, as you probably want to see these columns in your application you will probably need to translate the concept to whatever programming language and database client interface you are using.

David J.

Re: Constructing colum name as alias

From
chris
Date:
font{ line-height: 1.6; } ul,ol{ padding-left: 20px; list-style-position: inside; }
A flexible way to application, don’t know the actually value🤦‍♂️ see below：

select * from (Select ('Status as on '||date_part('day', (SELECT current_timestamp))||'th '||TO_CHAR(current_timestamp, 'Mon'))
union all
select now()::text from t1) foo limit 11;

Regards,
Chris
On 11/11/2021 22:16，David G. Johnston<david.g.johnston@gmail.com> wrote：
On Thu, Nov 11, 2021 at 7:14 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Nov 11, 2021 at 6:23 AM Theodore M Rolle, Jr. <stercor@gmail.com> wrote:
May we have an example?

Here are the docs detailing writing dynamic SQL commands in pl/pgsql.



Though, as you probably want to see these columns in your application you will probably need to translate the concept to whatever programming language and database client interface you are using.

David J.

Attachment