Thread: Dynamic column names in plpgsql
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;
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
"Derrick Betts" <derrick@grifflink.com> writes:
> I'm trying to write a function that uses dynamic column names to fetch
> the results within those columns.
The only way to do that in plpgsql is to construct a query string and
execute it with EXECUTE (or FOR ... IN EXECUTE). Anything else falls
foul of plpgsql's desire to cache execution plans for its queries.
The other PL languages make this easier, mainly because (a) their semantics
for database queries are always like EXECUTE, and (b) they all have more
extensive string-pushing facilities than plpgsql. So you should consider
plperl, pltcl, and/or plpython depending on which languages you know.
regards, tom lane