Thread: Need help with a function boolean --> text or array
Hi, I've got a table with a couple of boolean columns which represent attributes of entities. Now I need to list those entities with the attributes cumulated into one text-column. How could I do this? Silly example: table: pizzas id integer, name text, has_olive boolean, has_cheese boolean, has_onion boolean, has_salami boolean, has_egg boolean, has_extra_hot_chilies boolean .... My view should look like this: id, name, addons ... 11, 'Salami', 'Cheese, Salami' 42, 'Fire', 'Cheese, Onion, Salami, Extra hot chilies' ... Now I need a function that adds strings with commas in between (but not at the front or end) acording to the boolean-columns into one text-column that appears only in the view but isn't stored permanently. The function has to work on the current record of the view but without having to give it every single boolean as parameter. Something like this would be nice: SELECT id, name, fctAddons() FROM pizzas; I'm not very hot in stored procedures, yet. Could someone give me a clue how to get this done? Regards Andreas
On Jan 23, 2008 7:19 AM, Andreas <maps.on@gmx.net> wrote:
Uh,Hi,
I've got a table with a couple of boolean columns which represent
attributes of entities.
Now I need to list those entities with the attributes cumulated into one
text-column.
How could I do this?
select id, name, (case when has_olive then 'Olive' else '' end) ||
(case when has_cheese then ', Cheese' else '' end) ||
(case when has_onion then ', Onion' else '' end) ||
(case when has_salami then ', Salami' else '' end) ||
(case when has_egg then ', Egg' else '' end) ||
(case when has_extra_hot_chilies then ', Extra hot chilies' else '' end) as addons
from pizzas;
Patch:
select id, name, rtrim(leading ' ,' from
(case when has_olive then 'Olive' else '' end) ||
(case when has_cheese then ', Cheese' else '' end) ||
(case when has_onion then ', Onion' else '' end) ||
(case when has_salami then ', Salami' else '' end) ||
(case when has_egg then ', Egg' else '' end) ||
(case when has_extra_hot_chilies then ', Extra hot chilies' else '' end)) as addons
from pizzas;
select id, name, rtrim(leading ' ,' from
(case when has_olive then 'Olive' else '' end) ||
(case when has_cheese then ', Cheese' else '' end) ||
(case when has_onion then ', Onion' else '' end) ||
(case when has_salami then ', Salami' else '' end) ||
(case when has_egg then ', Egg' else '' end) ||
(case when has_extra_hot_chilies then ', Extra hot chilies' else '' end)) as addons
from pizzas;
Doesn't look too fancy though...
On 23/01/2008, Andreas <maps.on@gmx.net> wrote: > My view should look like this: > id, name, addons > ... > 11, 'Salami', 'Cheese, Salami' > 42, 'Fire', 'Cheese, Onion, Salami, Extra hot chilies' > ... Looks primarily like a normalisation problem to me. I'd be going for a pizzaname-table, an ingredient-table and a pizza-table that combines these m:n and then worry about the view ... > Regards > Andreas Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm
Andrej Ricnik-Bay wrote: > On 23/01/2008, Andreas <maps.on@gmx.net> wrote: > >> My view should look like this: >> id, name, addons >> ... >> 11, 'Salami', 'Cheese, Salami' >> 42, 'Fire', 'Cheese, Onion, Salami, Extra hot chilies' >> ... >> > Looks primarily like a normalisation problem to me. I'd be > going for a pizzaname-table, an ingredient-table and a pizza-table > that combines these m:n and then worry about the view ... > The pizza-table was a rather simplified example. The real table is a questinnaire that has a number of single fields and some distinct groups of booleans. For easy reading some of those boolean-groups should be rolled into one text field each but additionally every boolean should still appear as single column, too. It wasn't my idea. I just do the dirty work. ;) Further normalisation would run me into crosstab issues and this is also a white spot for me. Could you give me a sketch how a good design would represent such a questinnaire? I understand the m:n approach. My problem would rather be how to get a big view out of this m:n design. The current design also is ambiguous in the aspect that the frontend-app just knows boolean as Yes/No so TRUE=Yes but FALSE could be No as well as Unknown. Regards Andreas
On 24/01/2008, Andreas <maps.on@gmx.net> wrote: > Andrej Ricnik-Bay wrote: > > On 23/01/2008, Andreas <maps.on@gmx.net> wrote: > > > >> My view should look like this: > >> id, name, addons > >> ... > >> 11, 'Salami', 'Cheese, Salami' > >> 42, 'Fire', 'Cheese, Onion, Salami, Extra hot chilies' > >> ... > >> > > Looks primarily like a normalisation problem to me. I'd be > > going for a pizzaname-table, an ingredient-table and a pizza-table > > that combines these m:n and then worry about the view ... > > > > The pizza-table was a rather simplified example. > The real table is a questinnaire that has a number of single fields and > some distinct groups of booleans. > For easy reading some of those boolean-groups should be rolled into one > text field each but additionally every boolean should still appear as > single column, too. > It wasn't my idea. I just do the dirty work. ;) > > Further normalisation would run me into crosstab issues and this is also > a white spot for me. > Could you give me a sketch how a good design would represent such a > questinnaire? > I understand the m:n approach. > My problem would rather be how to get a big view out of this m:n design. I think that Andreas' response to Philips question in http://archives.postgresql.org/pgsql-sql/2007-12/msg00106.php should get you started on the concatenation .... as for the design: what is the cross-tab problem you'd see? > The current design also is ambiguous in the aspect that the frontend-app > just knows boolean as Yes/No so TRUE=Yes but FALSE could be No as well > as Unknown. That sounds dangerously stupid :} > Regards > Andreas Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm