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

Thanks Stuart,

That's a good chunk of info to digest! :> Unfortunately, I've spent minimal time learning PostgreSQl -- I've been so
busycoding PHP and I've used only rudimentary functions such as SELECT, UPDATE in accessing the db. 

Now that I'm nearly finished the coding, I'll have to go back and see if I code simpler and more securely.

For example, I've used SELECT table for authentication of username & password. I've read somewhere that the use of VIEW
mightbe more secure, but I needed to complete the basic website structure first. 

You were correct in assuming:

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

Strangely, the way it's set up now, it does this now without the whole history of sponsors displayed for the 'rap'
person.I'm going to try what you've suggested and see what comes of it. 

Btw, am I supposed to 'snip' some of this message -- it's getting awfully long!

Regards,
Andre

On Wednesday 08 May 2002 12:35 pm, you wrote:
> > -----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/

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