Re: Appending values non-destructively - Mailing list pgsql-novice

> -----Original Message-----
> From: Andre Dubuc [mailto:aajdubuc@webhart.net]
>
> Thanks Stuart,
>
> The test idea sounds like the way I want to go, but I've
> already set -up the
> table so that the field "sponsor" is int4. Would your idea
> still work?
>
> My current setup is with two tables: "rap" (that has the
> primary key) and
> "sponsor" (that has secondary key in relation to "rap"). I
> didn't bother
> including this info, since the question I'm dealing with
> concerns only a
> particular field in the secondary that may (only
> incidentally) have impact on
> the primary table.

It is relevant as to know exactly what you are wanting to do with this data
so that an appropriate strategy can be suggested. Finding that you can't do
what you want could mean that your relational model is incorrect in some
way. Of course an email saying everything about everything would be a bit
much to wade through :). However the schema for table would be a help, as
well as knowing what this data represents and what queries are intended for
it. :)

>
> I suppose my question was more generic: how to append say,
> numeric values in
> an 'int4' field without destroying the current values. When I
> tried something
> like:
>
> UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE ....

This would only work with a text field, varchar or char and it should have
been:
UPDATE tbl_name SET sponsor=sponsor || ', 567' WHERE ....
The double quotes came from me working on plpgsql to much.....

>
> I got an error message near the separator; so, my question . . .
>
> I gather I can use the "," separator before the number I wish to add?
>
> Terribly sorry if I'm not very coherent in asking the
> question -- I've left
> this problem toward the end of my database website design. My
> mind is sort of
> blotto.
>
> Thanks for the quick response,
> Andre

If you are using it to reference another table then this won't work (not
without parsing overhead anyway...)
One question is how you are wanting to use this information. If you just
insert the new values into the sponsor table rather than updating you can do
a left join something like this:
SELECT * FROM rap LEFT JOIN sponsor on rap.pk=sponsor.rap_pk_refernce;
This will return a row for each sponsor, which is probably what you want if
each rap can have more than one sponsor

If however you are just wanting historical information and are not wanting
everything every time you query then do something like the following:
(Not knoeing your actual field names, I've made them up):
CREATE TABLE sponsor_history (
        pk SERIAL,
        sponsor_id int4,
        rap_pk    int4,
        dt timstamp DEFAULT now(),
        PRIMARY KEY (pk)
        );

CREATE RULE spnsr_updt_rl AS ON UPDATE TO sponsor
WHERE OLD.sponsor_id<>NEW.sponsor_id OR OLD.rap_pk<>NEW.rap_pk
OR NEW.sponsor IS NULL AND NOT OLD.sponsor_id IS NULL
OR NEW.rap_pk IS NULL AND NOT OLD.rap_pk IS NULL
DO
INSERT INTO sponsor_history (sponsor_id,rap_pk) VALUES
(OLD.sponsor_id,NEW.sponsor_id);

CREATE RULE spnsr_updt_rl AS ON DELETE TO sponsor
DO
INSERT INTO sponsor_history (sponsor_id,rap_pk) VALUES
(OLD.sponsor_id,NEW.sponsor_id);

This will keep a timestamped history of when sponsor become replaced or
deleted.
Hope this helps,
- Stuart
P.S. I'm including the mailing list so that this will end up searchable in
the archives if any one has a similar problem in the future,& in case
someone else is able to over better/different advice.

>
> On Wednesday 08 May 2002 06:58 am, you wrote:
> > Hello,
> >     This sounds like a misunderstanding of how relational databases
> > work.
> > You have given no idea of the current schema but assuming
> that you have a
> > table with sponsor currently in it which we'll call tbl_a.
> We'll also
> > assume it has a primary key called pk_a. What you could do is create
> > another table as follows:
> > CREATE tbl_b (
> >     pk_b    SERIAL,
> >     sponsor    int4,
> >     pk_a        int4,
> >     PRIMARY KEY (pk_b)
> >     );
> > This table will have a 0 to n relationship with tbl_a by
> way of pk_a.
> > If you want you could use foriegn keys to enforce a relationship.
> > When ever you want to change the sponsor field you just
> INSERT another
> > value into this table (eg INSERT (sponsor,pk_a) VALUES
> (sponsor_id,<tbl_a's
> > pk_a>);
> > You can tell which is the currrent sponsor as this will
> have the highest
> > value of pk_b.
> > Alternatively you could UPDATE the record in sponsor, while either
> > INSERTing the last value into sponsor, or alternatively the
> new value
> > (which would require you to do this on INSERTS into tbl_a
> as well). This
> > approach has the advantage of speedier queries to find the
> current sponsor.
> > Also rather than having the application doing the updates
> you could use
> > triggers to automatically do the INSERTS into tbl_b.
> > Of course if you are infact just wanting a text
> representation of these
> > numbers you could just do something like:
> > UPDATE tbl_name SET sponsor=sponsor || '', 567'' WHERE ....
> > Hope this helps,
> > - Stuart
> >
> > > -----Original Message-----
> > > From: Andre Dubuc [mailto:aajdubuc@webhart.net]
> > >
> > > Using PostgreSQL 7.2 with PHP 4.1.2,  I would like to use the
> > > UPDATE function
> > > to add values to a field in a row that already has a value or
> > > values, i.e.:
> > > in a field named 'sponsor', the current value is '2588'. I
> > > would like the new
> > > value in 'sponsor' to become '2588, 2961', and later , '2588,
> > > 2961, 3166' etc.
> > >
> > > From my newbie reading, I haven't discovered whether it is
> > > possible to append
> > > values to a field without destroying the current value, and
> > > further, whether
> > > comma separators (or any sprators for that fact) are allowed
> > > or even possible.
> > >
> > > If some kind guru could help out with this simple question, I
> > > would greatly
> > > appreciate a small example code showing how to do it.
> > >
> > > Tia,
> > > Andre
>
> --
> Please pray the Holy Rosary to end the holocaust of abortion.
> Remember in your prayers the Holy Souls in Purgatory.
>
> May God bless you abundantly in His love!
> For a free Cenacle Scriptural Rosary Booklet:
http://www.webhart.net/csrb/