Re: Conditional rule?

On Sat, 29 Jul 2000, Andr Nss wrote:
> This seems ok to me, but I still don't like the invisibility as I pointed
> out above. If someone else was to continue my work how would they be able to
> see the triggers and procedures? They don't show up on "\d tablename", so it

SELECT prosrc FROM pg_proc WHERE proname = 'mytrigger';

The hidden pg_proc table contains all the info about the triggers.

Like in psql, you can do:

\o triginfo
SELECT * FROM pg_proc WHERE proname = 'mytrigger';
\o

and the output will go into file triginfo so you can view it better in a text
editor.

I haven't tested this myself yet, but I think you can UPDATE a record in
pg_proc to update the trigger prosrc. Then, to have it take effect, you'd
have to stop and start the database since PL/pgSQL is bytecode compiled on the
first execution and cached in the running backends.

> is very tempting to simply do this in PHP (i.e. managing the
> news_unpublished table with PHP.) I have the same issue with constraints, as
> they are invisible too.
>

You can use pg_dump -s to dump only the schema (definitions) of your database
and it will list all the table declarations, contraints, trigger procedures
etc.

> So, it comes down to something like this; I would really like to use
> constraints and trigger procedures, but in order to do so I need to keep a
> very close eye on my database, this means that I might get into trouble if I
> need to do some changes to this system after several months -- will I
> remember all these hidden behaviours? By doing it in PHP all the behaviour
> will be there in code, all visible and (hopefully) understandable to both
> myself and others.
>

Well, the problem with implementing "business rules" in php is that you'd
have to also implement those rules/procedures in all of your scripts that you
ever make for the database. If one script misbehaves or someone forgets
to update one of the scripts, then there are problems. Or, its just a lot
more work to have to maintain all the scripts. With the trigger procs, the
logic is implemented in one central place where no php scripts etc can
circumvent the rules/logic in the triggers.

PHP can provide a first level check that things are done right, but the trigger
procs in the database can provide the final check.

--
- Robert