I have a lot of tables of the same structure that represent weekly states of
a certain system. I'd like to write a function that would take field name,
number of weeks and return history of that field values as a single row. I
imagine something like this:
SELECT * FROM history('temperature', 10);
This should give me 11-column row, with first column being 'temperature',
and then its values for past ten weeks. Being able to use a set or SELECT in
place of paramater name and get several rows for different fields would be
even better.
I've read through the documentation, but how to do it is still unclear to
me. It looks like I should create and populate a RECORD-type variable inside
my function, but as I try to do this, I get the following errors:
WARNING: Error occurred while executing PL/pgSQL function test
WARNING: line 5 at assignment
ERROR: record "history_data" is unassigned yet - don't know its tuple
structure
How can I tell it what the structure will be? Issue a dummy "create table",
or there's a more elegant method?
Best regards,
Egor Shipovalov.