Thread: FW: PLPGSQL

FW: PLPGSQL

From

Shaun Clements

Date:

23 March 2005, 16:55:09

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.

Any suggestions

Kind Regards,
Shaun Clements

-----Original Message-----
From: Sean Davis [mailto:sdavis2@mail.nih.gov]
Sent: 23 March 2005 03:33 PM
To: Shaun Clements
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] PLPGSQL

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

Re: FW: PLPGSQL

From

Richard Huxton

Date:

23 March 2005, 17:19:24

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.
>
> Any suggestions

Use pl/tcl/perl/python or similar rather than plpgsql - it's not good
for this sort of thing. Pick whichever language you are most familiar
with, they should all cope fine with this sort of problem.

--
   Richard Huxton
   Archonet Ltd