By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,781 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,781 IT Pros & Developers. It's quick & easy.

Stored procedure advice needed

P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a

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

Nov 11 '05 #2

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.