Thread: PLPGSQL

Is there no way in pgplsql
to call on a dynamic column.
I need to be able to dynamically determine the latest month column within a dataset, and to get that columns data.
I am unfamiliar with other languages within Postgres

Kind Regards,
Shaun Clements

On Mar 23, 2005, at 7:56 AM, Shaun Clements wrote:

> Is there no way in pgplsql
> to call on a dynamic column.
> I need to be able to dynamically determine the latest month column
> within a dataset, and to get that columns data.
> I am unfamiliar with other languages within Postgres
>
>  Kind Regards,
> Shaun Clements
>

You can get all the column names for a table called 'testtable' using:

select a.attname
    from
        pg_attribute a,
        pg_class c
    where
        a.attrelid=c.oid and
        a.attnum>0 and
        c.relname='testtable';

You can then decide what column to use based on whatever logic you
like.  You will then need to construct the SQL statement using ||
(concatenate) and execute it using EXECUTE.

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

Does this help?  Another simpler way to do this would be a different
table structure where you put the month in a column by itself rather
than a different column for each month.

Sean

On Mar 23, 2005, at 8:51 AM, Shaun Clements wrote:

> Hi Sean
>
> Ive chosen the table structure on purpose.
> Im transforming data from one table to another. The problem is still
> there.
> I receive the column name from a query in one table, and then need to
> update the table with that column name in another.
>
> This needs to be done dynamically as part of a loop. So the column
> name needs to be called as a variable.
> Im stuck.
>

The answer is here to allow you to construct SQL statements from parts:

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

You can pass your column name as an argument to a function that does
what you want and then concatenate it with whatever else you want in
your SQL.  Then just EXECUTE the resulting statement.

Sean