Dave wrote:
"Hannes Dorbath" <light@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:4707FFF6.7010402@xxxxxxxxxxxxxxxxxxxxxxx
Dave wrote:
e.g. if an id of Oranges changes from '5' to '24', the tb1_column rows
will
get changed in the above example row to:
a:5:{i:0;s:1:"9";i:1;s:2:"24";i:2;s:2:"11";i:3;s:2:"100";i:4;s:2:"10";}
No offense, but that sounds like sick application design. Anyway, a
plphp trigger can do it:
No offense, but that sounds like sick application design
Offense taken :)
Well, you don't know the whole picture, so I can see why this would look bad
to you.
I don't know the whole picture either, but I'll side with Hannes because
I can't think of any circumstance where it's a good idea.
> That's why asked any other ideas.
The problem I need to solve is this:
I have main table with lets say:
11 | Pears
100 | Plums
9 | Apples
5 | Oranges
10 | Cranberries
OK
Now, I need to create another table with options for each of the above. So
for the apples:
ID | colors_available | favourite_color |
kind | favourite kind | Other similar options ...
9 | red, green, yellow, ...infinity | red | Granny smith, Golden delicious,
... infinity | Granny smith | Other similar values
What I'm trying to do is to keep the options for each froot in one row, as
opposed to creating separate table for colors, kind, etc.
Why?
I realize that I could create separate tables for colors, kind, etc, and
separate tables for favourite colors and favourite kind, but this would
involve accessing four different tables in this case.
Oh no! Accessing multiple tables in a relational database, madness!
> I guess, I can always
set up views with joins, but could not come up with a good way to query this
in a one returned row, e.g.:
ID | Name | colors | fav_col | kind
| fav_kind | etc.
11 | Apples | red, green, etc. | red | Granny smith, Golden
delicious.. | Granny smith | etc.
If you don't like tha array_accum() option described in the manuals, you
can build a more direct text-aggregator.
CREATE OR REPLACE FUNCTION agg_text(text, text) RETURNS text AS
$$
SELECT CASE
WHEN ($1 = '') THEN $2
ELSE $1 || ', ' || $2
END;
$$ LANGUAGE 'SQL' IMMUTABLE;
CREATE AGGREGATE agg_text (sfunc1=agg_text, basetype=text, stype1=text,
initcond1='');
SELECT group_column, agg_text(my_text_column) FORM my_table GROUP BY
group_column;
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
