Hi,

I have a table1 with a tb1_column that stores PHP serialized data that are
unique integers.
| tb1_column |
-----------
a:5:{i:0;s:1:"9";i:1;s:2:"5";i:2;s:2:"11";i:3;s:2: "100";i:4;s:2:"10";}

I also have a table2 with unique integers (ids) in tb2_column
| tb2_column | descr |
----------
11 | Pears
100 | Plums
9 | Apples
5 | Oranges
10 | Cranberries

What I'm trying to do is to create a foreign key on tb1_column so that if a
number in tb2_column changes or gets deleted, it cascades to the appropriate
segment of the serialized data.
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";}

Is this possible? Can anyone suggest a function that would do that?

If not what is the alternative that will have a similar effect?

Thanks

Dave

Dave wrote:
> What I'm trying to do is to create a foreign key on tb1_column so that if a
> number in tb2_column changes or gets deleted, it cascades to the appropriate
> segment of the serialized data.
> 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:

http://www.commandprompt.com/community/plphp


--
Best regards,
Hannes Dorbath

"Hannes Dorbath" <light@theendofthetunnel.de> wrote in message
news:4707FFF6.7010402@theendofthetunnel.de...
> Dave wrote:
>> What I'm trying to do is to create a foreign key on tb1_column so that if
>> a
>> number in tb2_column changes or gets deleted, it cascades to the
>> appropriate
>> segment of the serialized data.
>> 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:
>
> http://www.commandprompt.com/community/plphp
>
>
> --
> Best regards,
> Hannes Dorbath


> 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. 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

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.
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. 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.

On Oct 8, 2007, at 8:33 , Dave wrote:

> 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.

You can either do it in your middleware or use array_accum.

http://www.postgresql.org/docs/8.2/static/xaggr.html

The archives have examples of doing things like this.

Note that *this* is the problem you're trying to solve, not the
serialization issue you're having above. I recommend changing your
schema.

Michael Glaesemann
grzm seespotcode net



---------------------------(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

Dave wrote:
> "Hannes Dorbath" <light@theendofthetunnel.de> wrote in message
> news:4707FFF6.7010402@theendofthetunnel.de...
>> 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

On Oct 8, 2007, at 12:08 PM, Michael Glaesemann wrote:

>
> On Oct 8, 2007, at 8:33 , Dave wrote:
>
>> 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.
>
> You can either do it in your middleware or use array_accum.
>
> http://www.postgresql.org/docs/8.2/static/xaggr.html
>
> The archives have examples of doing things like this.
>
> Note that *this* is the problem you're trying to solve, not the
> serialization issue you're having above. I recommend changing your
> schema.

I, also, agree. The "problem" you're trying to avoid with having
separate tables for colors, kinds, etc. is exactly what a relational
database is for.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings