Обсуждение: [GENERAL] column names query

Hi,

is there a simple way to retrieve column names from a query - basically the way psql adds
column headings when I do a select?

Best regards
Wolfgang Hamann

On Thu, Sep 7, 2017 at 9:18 AM,  <hamann.w@t-online.de> wrote:
> is there a simple way to retrieve column names from a query - basically the way psql adds
> column headings when I do a select?

How do you do the query? I mean, JDBC, PERL? After all psql is just a
C program doing a query using libpq and can do it, we may provide some
useful info if you show yours first.

Francisco Olarte.

>> On Thu, Sep 7, 2017 at 9:18 AM,  <hamann.w@t-online.de> wrote:
>> > is there a simple way to retrieve column names from a query - basically the way psql adds
>> > column headings when I do a select?
>>
>> How do you do the query? I mean, JDBC, PERL? After all psql is just a
>> C program doing a query using libpq and can do it, we may provide some
>> useful info if you show yours first.
>>
>> Francisco Olarte.

Hi,

I imagine this situation:
I start working on a query... when I am happy with the result, I see a table (with headers) in psql.
Now I can do
\copy (select .... my query here) to /tmp/testfile1
I would like to do something like
\copy (select heading(select .... my query here)) to /tmp/heading_testfile1

Best regards
Wolfgang Hamann

>> SELECT table_name, column_name
>> FROM information_schema.columns
>> WHERE table_name = 'your_name';
>>
>> ----- Original Message -----From: hamann w <hamann.w@t-online.de>To: pgsql-general@postgresql.orgSent: Thu, 07 Sep
201707:18:12 -0000 (UTC)Subject: [GENERAL] column names query 
>>
>>
>>
>> Hi,
>>
>> is there a simple way to retrieve column names from a query - basically the way psql addscolumn headings when I do a
select?
>>
>> Best regardsWolfgang Hamann
>>
>>
>>
>> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make changes to your
subscription:http://www.postgresql.org/mailpref/pgsql-general
>>

Hi,

I am already using something like this (but using pg_attribute table) to retrieve current table layout
for static tables. At the moment I am looking for a solution for ad hoc queries

Example query
select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3 from a left join b on ....
Expected response
col1 mycol3

Obviously, I could save the result into a temp table and retrieve the table's heading.

Best regards
Wolfgang Hamann

Hi Hamman:

On Thu, Sep 7, 2017 at 3:17 PM,  <hamann.w@t-online.de> wrote:
> I would like to do something like
> \copy (select heading(select .... my query here)) to /tmp/heading_testfile1

It's already been pointed out, you can do something using CSV copy
with headers ( Why headers are not allowed in other formats remains a
mistery to me), and LIMIT 0 ( I would try adding AND FALSE to the
where clause better, it may lead to faster response, although I doubt
it) .

Francisco Olarte.

On 9/7/2017 12:18 AM, hamann.w@t-online.de wrote:
> is there a simple way to retrieve column names from a query - basically the way psql adds
> column headings when I do a select?

if you're using libpq to do your queries, PQfname(*result,
column_number) returns the name of that column number.

there are equivalent functions in most other APIs.


--
john r pierce, recycling bits in santa cruz

>> On Thu, Sep 7, 2017 at 3:28 PM <hamann.w@t-online.de> wrote:
>> 
>> > Example query
>> > select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3
>> > from a left join b on ....
>> > Expected response
>> > col1 mycol3
>> >
>> 
>> This may be overkill, but works:
>> 
>> postgres=# \copy (select 1 as foo, 2 as bar limit 0) to stdout with (format
>> csv, header, delimiter ' ')
>> foo bar
>> 

Hi Manuel,

this works perfectly, many thanks

Wolfgang




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general