Обсуждение: Querying Headers

Hello,

I am curious if there is a postgresql function that will
return a list of header names based on an input query text.

ie:


select return_headers("SELECT name, date, shape FROM some_table;") as
headers;

returning:

headers
-------
name
date
shape


Thanks for any help.

Pw

pw wrote:
>
> Hello,
>
> I am curious if there is a postgresql function that will
> return a list of header names based on an input query text.
>
> ie:
>
>
> select return_headers("SELECT name, date, shape FROM some_table;") as
> headers;
>
> returning:
>
> headers
> -------
> name
> date
> shape
>
>
> Thanks for any help.
>
> Pw

As a supplementary comment to this:
This information can be extracted from the pg_catalog
in several steps as follows:

CREATE VIEW testview AS (SELECT name, date, shape FROM some_table);

SELECT distinct attname FROM pg_attribute, pg_class, testview WHERE
attrelid=pg_class.oid AND pg_class.relname='testview';

/*GET THE COLUMN HEADERS HERE AND RETURN THEM*/

DROP VIEW testview;

I was however hoping someone had already created a
function that was standard.


ie:
CREATE FUNCTION return_header_names(text) RETURNS SETOF string
     AS '
    CREATE VIEW testview AS ($1);
    SELECT distinct attname FROM pg_attribute, pg_class, testview WHERE
attrelid=pg_class.oid AND pg_class.relname='testview';
    '
     LANGUAGE SQL
     IMMUTABLE
     RETURNS NULL ON NULL INPUT;


Thanks again,


Pw

pw <p.willis@telus.net> writes:
>> I am curious if there is a postgresql function that will
>> return a list of header names based on an input query text.

No, but there is support for such things at the wire protocol level
--- specifically, Parse followed by Describe Statement would get you
the result column names and types.  How much of that is exposed by
the interface library you use would depend on the library; in recent
libpq versions you would use PQprepare then PQdescribePrepared.

            regards, tom lane