Thread: Stored procedure advice needed

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.

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.

This sounds an awful lot like crosstab functionality, Check out the
tablefunc module in contrib, I think there is something in there that
will help you with this.

Robert Treat

On Thu, 2003-08-21 at 16:11, Stephan Szabo wrote:
>
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL