updating rows while selecting
От | Janning Vygen |
---|---|
Тема | updating rows while selecting |
Дата | |
Msg-id | 0105091904310J.05990@janning обсуждение исходный текст |
Ответы |
Moving to 7.1.1
|
Список | pgsql-general |
Hi, i am new to concepts of SQL and postgresql. For tutorial purpose i wrote a little quotes database with columns: name, quote, timestamp a php html page should show every day another quote of the day. it works fine and like this: selecting a row with timestamp = today if there is no row select one without timestamp (this way new quotes inserted should be shown before older ones). if there is still no rows the result then update the oldest quote and ask again. Is it possible to put this php programm structure into the database?? I tried many different things but i dont know how to do it. I just always want to select autmagicall a new quote of the day! Is there something like an if, then, else statement in SQL? I knwo that SQL is not a procedural language, but is there data view way to achieve something like this? Your help would be a great step for me in understanding SQL. thanks in advance Janning p.s.: (zitate = quotes, zitat = quote, just the german translation...) <?php $conn = pg_pconnect ("dbname=cff user=janning"); $result = pg_Exec ($conn, " SELECT name, zitat FROM zitate where timestamp = CURRENT_DATE; "); if (! pg_numrows ($result)) { pg_Exec ($conn, " UPDATE zitate SET timestamp = CURRENT_DATE WHERE oid = (SELECT DISTINCT ON (timestamp) oid from zitate where timestamp = NULL); "); $result = pg_Exec ($conn, " SELECT name, zitat FROM zitate where timestamp = CURRENT_DATE; "); if (! pg_numrows($result)) { pg_Exec ($conn, " UPDATE zitate SET timestamp = CURRENT_DATE WHERE timestamp = ( select distinct on (timestamp) timestamp from zitate where timestamp = (select min(timestamp) from zitate)); "); $result = pg_Exec ($conn, " SELECT name, zitat FROM zitate where timestamp = CURRENT_DATE; "); }}} ?>
В списке pgsql-general по дате отправления: