Thread: building a row with a plpgsql function

Hi,

We have a table collectiong all details of an item:
Here is a simplified representation of the tables
Tables:

items
------
item_id


item_details
------------
item_detail_id
item_id
item_detail_name
item_detail_value


That way, we can easily have a variable number of detail for the items (some can have 10 details, some only one).
I wondered if it was possible to build a row with all details of an item. It would then be possible to do

select * from details(5);
which would return all details of item with item_id 5:

serial | customer | type
0012   | Mr Doe   | standard


because we have this :

select item_id, item_detail_name, item_detail_value from item_details where item_id=5;

item_id | item_detail_name | item_detail_value
-----------------------------------------------
      5 |           serial |              0012
      5 |         customer |            Mr Doe
      5 |             type |          standard



Thanks for your feedback.

Raph

Raphael Bauduin wrote:
> Hi,
>
> We have a table collectiong all details of an item:
> Here is a simplified representation of the tables
> Tables:
>
> items
> ------
> item_id
>
>
> item_details
> ------------
> item_detail_id
> item_id
> item_detail_name
> item_detail_value
>
>
> That way, we can easily have a variable number of detail for the items
> (some can have 10 details, some only one).

Be carefull: With this design, you can't declare any indexes
and constraints on an item-detail !
You also can't use different types for the item-details.
Triggers would be more complicated.

Consider to make the item-details to table-columns.

Michael Kleiser wrote:
>
> Raphael Bauduin wrote:
>
>> Hi,
>>
>> We have a table collectiong all details of an item:
>> Here is a simplified representation of the tables
>> Tables:
>>
>> items
>> ------
>> item_id
>>
>>
>> item_details
>> ------------
>> item_detail_id
>> item_id
>> item_detail_name
>> item_detail_value
>>
>>
>> That way, we can easily have a variable number of detail for the items
>> (some can have 10 details, some only one).
>
>
> Be carefull: With this design, you can't declare any indexes
> and constraints on an item-detail !
> You also can't use different types for the item-details.
> Triggers would be more complicated.
>
> Consider to make the item-details to table-columns.
>

That was our first choice design, but we really need that extreme flexibility with the details.
Item details specs are a moving target, with impossibility to prevent changes.... Items are also very
different, but if we had to have one table per item, it would be unmanageable. New items types are
regularly created too, which would ask the creation of new tables.

About the different types we could have to store as details: we have a table with date details.

I'm also very cautious with that design, and am aware of the risks in it, but
we have thought about it a long time, and it seems to be the best in our situation.
We are in the final stage in this DB design and all tests until now have been positive.

Thanks for the warning anyway ;-)
I'm still open to an alternative solution, but we can't give up the flexibility this design gives us.

Raph

Raphael Bauduin wrote:
> That was our first choice design, but we really need that extreme
> flexibility with the details.
> Item details specs are a moving target, with impossibility to prevent
> changes.... Items are also very different, but if we had to have one
> table per item, it would be unmanageable. New items types are regularly
> created too, which would ask the creation of new tables.
> About the different types we could have to store as details: we have a
> table with date details.
>
> I'm also very cautious with that design, and am aware of the risks in
> it, but we have thought about it a long time, and it seems to be the
> best in our situation.
> We are in the final stage in this DB design and all tests until now have
> been positive.

See contrib/tablefunc, and read through the following link for examples
similar to what you are doing:

http://www.joeconway.com/pres_oscon_2004-r1.pdf
http://www.joeconway.com/flex.sql

HTH,

Joe

Joe Conway wrote:
> Raphael Bauduin wrote:

[snip]

>
>
> See contrib/tablefunc, and read through the following link for examples
> similar to what you are doing:
>
> http://www.joeconway.com/pres_oscon_2004-r1.pdf
> http://www.joeconway.com/flex.sql

Seems to be exactly what I need! I'll look further at it.

Thanks!

Raph


>
> HTH,
>
> Joe

Raphael Bauduin wrote:
> Joe Conway wrote:
>
>> Raphael Bauduin wrote:
>
>
> [snip]
>
>>
>>
>> See contrib/tablefunc, and read through the following link for
>> examples similar to what you are doing:
>>
>> http://www.joeconway.com/pres_oscon_2004-r1.pdf
>> http://www.joeconway.com/flex.sql
>
>
> Seems to be exactly what I need! I'll look further at it.

I've tested it and it does exactly what I want, but there is one problem in my case:
I need to specify the column definitions. But in my case the number of columns is
variable.  I call crosstab like that:

select * from crosstab(
'select item_id, detail_name, detail_value from vw_item_details where item_id=10',
'select detail_name from item_details where item_detail_id = (select item_detail_id from vw_item_details where
item_id=10)'
) AS (  ...  )

If I have to write the AS ( ... ) part of the query, it means that each time we add a detail
to an item, I'll have to modify this query to make the detail appear.

In case I was not clear in my description, it is similar to the example given in the README.

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);

Working on the following data:
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
insert into cth values(DEFAULT,'test2','02 March 2003','test_result','FAIL');
insert into cth values(DEFAULT,'test2','02 March 2003','test_startdate','01 March 2003');
insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');

you have 4 attributes defined.But if 'SELECT DISTINCT attribute FROM cth ORDER BY 1' returns a 5th attribute,
you'll have to rewrite the AS ( .. ) part of the query to make this 5th attribute
appear in the results. Is there a way to avoid that?

I could say that all values returned are of type text, so all columns would be text.
Is it possible to generate the AS ( .. ) part dynamically? Or hould I modify the C code
(I hope not ;-)

Thanks in advance for your help.

Raph




> Thanks!
>
> Raph
>
>
>>
>> HTH,
>>
>> Joe
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Raphael Bauduin wrote:
> Raphael Bauduin wrote:
>
>> Joe Conway wrote:
>>
>>> Raphael Bauduin wrote:
>>
>>
>>
>> [snip]
>>
>>>
>>>
>>> See contrib/tablefunc, and read through the following link for
>>> examples similar to what you are doing:
>>>
>>> http://www.joeconway.com/pres_oscon_2004-r1.pdf
>>> http://www.joeconway.com/flex.sql
>>
>>
>>
>> Seems to be exactly what I need! I'll look further at it.
>
>
> I've tested it and it does exactly what I want, but there is one problem
> in my case: I need to specify the column definitions. But in my case the
> number of columns is variable.  I call crosstab like that:
>
> select * from crosstab(
> 'select item_id, detail_name, detail_value from vw_item_details where
> item_id=10',
> 'select detail_name from item_details where item_detail_id = (select
> item_detail_id from vw_item_details where item_id=10)'
> ) AS (  ...  )
> If I have to write the AS ( ... ) part of the query, it means that each
> time we add a detail
> to an item, I'll have to modify this query to make the detail appear.
>
> In case I was not clear in my description, it is similar to the example
> given in the README.
>
> SELECT * FROM crosstab
> (
>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
> )
> AS
> (
>       rowid text,
>       rowdt timestamp,
>       temperature int4,
>       test_result text,
>       test_startdate timestamp,
>       volts float8
> );
>
> Working on the following data:
> create table cth(id serial, rowid text, rowdt timestamp, attribute text,
> val text);
> insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
> insert into cth values(DEFAULT,'test1','01 March
> 2003','test_result','PASS');
> insert into cth values(DEFAULT,'test1','01 March 2003','volts','2.6987');
> insert into cth values(DEFAULT,'test2','02 March 2003','temperature','53');
> insert into cth values(DEFAULT,'test2','02 March
> 2003','test_result','FAIL');
> insert into cth values(DEFAULT,'test2','02 March
> 2003','test_startdate','01 March 2003');
> insert into cth values(DEFAULT,'test2','02 March 2003','volts','3.1234');
>
> you have 4 attributes defined.But if 'SELECT DISTINCT attribute FROM cth
> ORDER BY 1' returns a 5th attribute, you'll have to rewrite the AS ( ..
> ) part of the query to make this 5th attribute
> appear in the results. Is there a way to avoid that?
>
> I could say that all values returned are of type text, so all columns
> would be text.
> Is it possible to generate the AS ( .. ) part dynamically? Or hould I
> modify the C code
> (I hope not ;-)
>
> Thanks in advance for your help.
>
> Raph

A little update on what I do (in case someone gets in the same situation as I am).
Rather than writing the AS ( field type, ....) part of the query, I build it in my application
each time a crosstab query is issued.
For example for this query:

 SELECT * FROM crosstab
 (
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
 )
 AS
 (
    XXXXX
 );

the application code replaces the XXXXX by getting the results of
"SELECT DISTINCT attribute FROM cth ORDER BY 1" and iterating over
the attributes returned to build the columns list (all columns are text).

This works really fine. My problem now is that the query to get the attributes
is taking a looong time (2.7 seconds), and it is issued twice!

Would be great if someone could point me in the right direction to avoid this
duplication of queries. If not I'll continue to search and post my discoveries ;-)
One thing I'll try is to rework the query to get the returned fields (at the moment
it joins 4 or 5 tables, but it could be faster to issue 2 simpler queries to get
the same result)

ciao!


Raph

Raphael Bauduin wrote:
> A little update on what I do (in case someone gets in the same situation
> as I am).
> Rather than writing the AS ( field type, ....) part of the query, I
> build it in my application
> each time a crosstab query is issued.
> For example for this query:
>
> SELECT * FROM crosstab
> (
>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
> )
> AS
> (
>    XXXXX
> );
>
> the application code replaces the XXXXX by getting the results of
> "SELECT DISTINCT attribute FROM cth ORDER BY 1" and iterating over
> the attributes returned to build the columns list (all columns are text).
>
> This works really fine. My problem now is that the query to get the
> attributes
> is taking a looong time (2.7 seconds), and it is issued twice!
>

Sorry for the slow response. Couple of thoughts:

1. As long as you are building the query in your application, use the
results of the distinct query to build the category sql as a UNION ALL
of literals -- e.g.:

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT ''temperature''
   UNION ALL SELECT ''test_result''
   UNION ALL SELECT ''test_startdate''
   UNION ALL SELECT ''volts'''
)
AS
(
   rowid text,
   rowdt timestamp,
   temperature int4,
   test_result text,
   test_startdate timestamp,
   volts float8
);

2. How often do new attributes show up? If it is relatively infrequent,
you might want to build a table ("materialized view") from
  "SELECT DISTINCT attribute FROM cth ORDER BY 1"
and then refresh it periodically.

Joe

Joe Conway wrote:
> Raphael Bauduin wrote:
>
>> A little update on what I do (in case someone gets in the same
>> situation as I am).
>> Rather than writing the AS ( field type, ....) part of the query, I
>> build it in my application
>> each time a crosstab query is issued.
>> For example for this query:
>>
>> SELECT * FROM crosstab
>> (
>>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>>  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
>> )
>> AS
>> (
>>    XXXXX
>> );
>>
>> the application code replaces the XXXXX by getting the results of
>> "SELECT DISTINCT attribute FROM cth ORDER BY 1" and iterating over
>> the attributes returned to build the columns list (all columns are text).
>>
>> This works really fine. My problem now is that the query to get the
>> attributes
>> is taking a looong time (2.7 seconds), and it is issued twice!
>>
>
> Sorry for the slow response. Couple of thoughts:
>
> 1. As long as you are building the query in your application, use the
> results of the distinct query to build the category sql as a UNION ALL
> of literals -- e.g.:
>
> SELECT * FROM crosstab
> (
>  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
>  'SELECT ''temperature''
>   UNION ALL SELECT ''test_result''
>   UNION ALL SELECT ''test_startdate''
>   UNION ALL SELECT ''volts'''
> )
> AS
> (
>   rowid text,
>   rowdt timestamp,
>   temperature int4,
>   test_result text,
>   test_startdate timestamp,
>   volts float8
> );
>

I've experimented a bit and I'm confident we can optimize the queries as needed.
the use of crosstab wil require some tweaking and optimising, but I think it's really
 worth it in our case.



> 2. How often do new attributes show up? If it is relatively infrequent,
> you might want to build a table ("materialized view") from
>  "SELECT DISTINCT attribute FROM cth ORDER BY 1"
> and then refresh it periodically.


the problem is that the attribute list depends of the item we display.

But the crosstab function does exactly what I need. I'll work with it
and look at optimize it later on. you might hear from me again at that time ;-)


Thanks for your help!

Raph


>
> Joe