Stored procedure advice needed

From: "Egor Shipovalov" <pgsql_list(at)eonline(dot)ru>
To: "Pgsql-General" <pgsql-general(at)postgresql(dot)org>
Subject: Stored procedure advice needed
Date: 2003-08-21 19:44:06
Message-ID: AKEFIKCGEAEFBICLBFBMKELBCGAA.pgsql_list@eonline.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2003-08-21 20:11:22 Re: Stored procedure advice needed
Previous Message Philip Boonzaaier 2003-08-21 19:37:02 Re: Bulk Insert / Update / Delete