in last 10 minutes
Example of a trigger in Postgres
Thanks to Priti Sawant for his help on this.create function modify_date() returns OPAQUE as '
new.modified_date := ''now'';
' language plpgsql;
create trigger modify_date_trig
before insert or update on pan_type
for each row
execute procedure modify_date();
This is Postgres' helpful way of telling you to recreate your view. Or it might mean that you should say "CREATE OR REPLACE" when you drop and create a function.ERROR: Relation "rl_resources_revisions" with OID 330733 no longer exists
Hierarchical queriesSee using tree_sortkey for OpenACS.
Getting around 16 argument limitSee how to update after an insert.
Or upgrade to Postgres 7.3.x, which doesn't have that limit.
DatesTo specify the present date and time, use CURRENT_TIMESTAMP. If you just want the date, use CURRENT_DATE.
select current_date + s.a as dates from generate_series(0,14,7) as s(a);
to_date vs. to_timestamp (also info on coalesce and truncate vs. date_trunc)
Upgrading from 7.2.x to 7.3.xUpgrade script that handles the function and view truncation problems
Remember that psql 7.3 doesn't like tabs
- If you get an error message like this during ./configure :
Then you need to install the libreadline4-dev package (which includes the header files you need). On Debian:
checking for readline... no
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.
apt-get install libreadline4-dev
Postgres query debugging
*.xql files are loaded into memory at start. They take precedence over queries quoted in *.tcl files.
Sometimes Oracle syntax or reserved words make it into postgresql queries. Here (is the start of a list) of equivalents
- Oracle > Postgres equivalent
- sysdate = current_timestamp
Querying multiple databases from OpenACShttp://openacs.org/forums/message-view?message_id=118550
Dealing with concurrent issuesHow do you make sure that user A and user B don't clobber each other's changes?