Dynamic column names in plpgsql

From: "Derrick Betts" <derrick(at)grifflink(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Dynamic column names in plpgsql
Date: 2006-01-31 04:01:37
Message-ID: 01a601c6261b$0a4e6480$0202a8c0@main
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm trying to write a function that uses dynamic column names to fetch the results within those columns. I have written the following, however, the result is the name of the column, not the value within the column. I've tried several ideas and searched all over looking for a solution. Does anyone know if there is one? I have looked into the
PQfnumber(const PGresult *res, const char *column_name); along with PQgetvalue(const PGresult *res, int row_number, int column_number); which would likely work, but I think it is designed for C code, not plpgsql. I'm using version 8.0.1 on Redhat.

FOR rec IN SELECT column_name FROM information_schema.columns WHERE table_name = 'contact_app_gfe' AND column_name like '%total' ORDER BY column_name
LOOP
holder = '';
SELECT rec.column_name INTO holder FROM contact_app_gfe WHERE contactid = 2057;
IF holder <> '' THEN
RAISE NOTICE 'Value: %', holder ; --this is giving me the column names
END IF;
END LOOP;
RETURN;

Thanks, Derrick

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno Wolff III 2006-01-31 05:49:01 Re: Upgrade to PG 8 before starting major development?
Previous Message Michael Glaesemann 2006-01-31 01:13:01 Re: Insert Text