Thread: PL/pgSQL

Hello everyone,

I am using PostgreSQL already for quiet a while and now I want to extend my
knowledge and make better use of PostgreSQL itself.


Here is a selection of the issues I try to solve:

- If I delete a record, I want to delete all its references. -- Is it right
that I have to use a function and a trigger; but how?
- I want to make sure an entry does not exist in the table, before adding a
new entry.
- When adding an entry generate another table's entry using some default
information.
- Automatically delete entries that are old.
- Update entries based on other tables' information.

I think all of these problems are not possible to solve with regular
SELECT, UPDATE and DELETE statements. So I guess what I am asking is, where
do I find some documentation on triggers and functions for PostgreSQL
7.0.2? Could you send me some sample code?

Thanks very much in advance for your time.

Regards,
Stephan
--
Stephan Richter
CBU - Physics and Chemistry Student
Web2k - Web Design/Development & Technical Project Management

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> - If I delete a record, I want to delete all its references. -- Is it right
> that I have to use a function and a trigger; but how?

Just say ON DELETE CASCADE when creating the reference. If you want a
row's references to be updated when you change the original row, you
should do ON UPDATE CASCADE, as well.

> - I want to make sure an entry does not exist in the table, before adding a
> new entry.

Select for it first, then do IF NOT FOUND. Or use a UNIQUE index on the
table, to keep the entries indexed. But watch out -- if any statement of
the script fails, it stops there.

> - When adding an entry generate another table's entry using some default
> information.

Store the default in some table, then do an INSERT ... SELECT.

> - Automatically delete entries that are old.

use DELETE WHERE x, where x is some definition of 'old'.

If you wanted this cleanup to happen without any continued queries, the
easiest way would be to 1) create a view which excludes 'old' entries, and
2) cleanup the 'old' entries before you vacuum each night.

> - Update entries based on other tables' information.

Not sure what you mean here. If you mean subselects, joins, or unions in
an update statement, that should work. Alternatively, do the selects you
need to get your answer in a Result, and then update from the Result.

> I think all of these problems are not possible to solve with regular
> SELECT, UPDATE and DELETE statements.

I beg to differ.

> So I guess what I am asking is, where do I find some documentation on
> triggers and functions for PostgreSQL 7.0.2?

Erm, did you try the main postgresql documentation? Perhaps you should
search it for 'triggers'? :o

Pl/PgSQL does not support utility statements such as create trigger,
however it will in postgresql 7.1.

Ian Turner
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5rRZ+fn9ub9ZE1xoRAh/6AKDJgeCq/7TUHWwgYmXFZpW4PWCAYgCgkUZv
Rh/lbMThWg8dgXjsehDNJN8=
=kw7x
-----END PGP SIGNATURE-----