Thread: Determining the names of columns in a dynamic query

Hi,

Sorry for cross posting, but seems nobody read the pgsql.sql NG.  Have
not received any response there so lets see if this create some reaction.

Is it possible in pgsql to have something to manipulate columns where
the column names will only be resolved during execution of the select
statement:

FOR rec IN (SELECT * FROM table)
LOOP
   -- This is what I would like to do
   FOR col IN (rec.column_names)
   LOOP
     IF col = 'blahblah' THEN
       -- Do something
     ELSEIF col = 'nextcol'
       --
     ELSE
       -- Other column
     END IF;
   END LOOP;
END LOOP;

For the above, it does not need to be exactly the same, but are there a
group of functions available to at least return in a dynamic query the
number of columns, name of a column given the ordinal number of the
column, etc?

TIA,

Johan Nel
Pretoria, South Africa.

My MTA says your email domain doesn't exist...

On Apr 24, 2009, at 1:27 PM, Johan Nel wrote:
> Sorry for cross posting, but seems nobody read the pgsql.sql NG.
> Have not received any response there so lets see if this create
> some reaction.
>
> Is it possible in pgsql to have something to manipulate columns
> where the column names will only be resolved during execution of
> the select statement:
>
> FOR rec IN (SELECT * FROM table)
> LOOP
>   -- This is what I would like to do
>   FOR col IN (rec.column_names)
>   LOOP
>     IF col = 'blahblah' THEN
>       -- Do something
>     ELSEIF col = 'nextcol'
>       --
>     ELSE
>       -- Other column
>     END IF;
>   END LOOP;
> END LOOP;
>
> For the above, it does not need to be exactly the same, but are
> there a group of functions available to at least return in a
> dynamic query the number of columns, name of a column given the
> ordinal number of the column, etc?

You didn't mention, but that looks like PLPGSQL, so I'll assume that
it is.

Unfortunately, the answer is no. You'd have to use something like PL/
Perl.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On 2009-04-24, Johan Nel <johan555.nel555@xsinet555.co.za> wrote:
> Hi,
>
> Sorry for cross posting, but seems nobody read the pgsql.sql NG.  Have
> not received any response there so lets see if this create some reaction.
>
> Is it possible in pgsql to have something to manipulate columns where
> the column names will only be resolved during execution of the select
> statement:

no.

> FOR rec IN (SELECT * FROM table)
> LOOP
>    -- This is what I would like to do
>    FOR col IN (rec.column_names)
>    LOOP


> For the above, it does not need to be exactly the same, but are there a
> group of functions available to at least return in a dynamic query the
> number of columns, name of a column given the ordinal number of the
> column, etc?

not in plpgsql, you have to use a different language like plpython,
plperl, or C.