Thread: newbie sql question: select every day another quote

for tutoral purpose i ve build a quotes table like this:

create table quotes (name text, quote text, lastdayshown date UNIQUE);
insert into quotes (name, quote) values ('caesar', 'veni vidi vici');
insert into quotes (name, quote) values ('hamlet', 'to be or not to be');

lastdayshown is always null on insert.
works fine. Now i want to select every day a different quote.

Is it possible to
select the row with lastdayshown = today
if no result
update one row with lastdayshown = null
else
update one row with oldest lastdayshown.

I want to put this procedural structure into sql, maybe creating a view, in
which the database user always sees the "Quote of the Day".

Maybe i am still thinking in procedural structures. Do i have to use PL/SQL
for it?

thanks in advance
janning

--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf

fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de

Am Samstag, 12. Mai 2001 14:37 schrieb Len Morgan:
> >lastdayshown is always null on insert.
> >works fine. Now i want to select every day a different quote.
> >
> >Is it possible to
> >select the row with lastdayshown = today
>
> SELECT * FROM quotes WHERE lastdayshown = now()::date ;
>
> >if no result

Hi,

thanks for your answer
i already manages this part :-)

> The "no result part" is harder.  You'll have to have some sort of client
> program that can tell you if the above quote returned a row or not.  Once
> you have that, if the above query did NOT return a row, you can use the
> following two steps:
>
> UPDATE quotes SET lastdayshown = now():: WHERE oid = (SELECT oid FROM
> quotes WHERE lastdayshown IS NULL LIMIT 1) ;

i managed this now in php. works fine
but the idea was to put this structure into the database, so the db user just
have to send one select and once a day there is an automagical update.

maybe its not possible...

thanks
janning

--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf

fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de

On Sat, 12 May 2001, Janning Vygen wrote:

> Am Samstag, 12. Mai 2001 14:37 schrieb Len Morgan:
> > The "no result part" is harder.  You'll have to have some sort of client
> > program that can tell you if the above quote returned a row or not.  Once
> > you have that, if the above query did NOT return a row, you can use the
> > following two steps:
> >
> > UPDATE quotes SET lastdayshown = now():: WHERE oid = (SELECT oid FROM
> > quotes WHERE lastdayshown IS NULL LIMIT 1) ;
>
> i managed this now in php. works fine
> but the idea was to put this structure into the database, so the db user just
> have to send one select and once a day there is an automagical update.

It will probably work to make a plpgsql function doing something like:

1.  Figure out the primary key of today's quote
2.  Update the database if necessary
3.  Return the key of today's quote

then you can SELECT * from quotes WHERE key = todays_key();
--
Tod McQuillin