Re: Stored procedure advice needed

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