Re: Stored procedure advice needed

On Thu, 21 Aug 2003, Egor Shipovalov wrote:

> 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

You can select a bunch of dummy data of the appropriate types into
history_data. However, it sounds like since you won't know the number of
columns (or possibly their types) until runtime you may need to do
something marginally complicated to make that work like a
for history_date in execute 'select ...' loop end loop type thing.