Re: [SQL] Conditional rule?

[André Næss]

- Original Message -
> > And this works as intended. There are however a few things that worries
me.
> > First of all, I can't seem to find any way to list the trigger and the
> > function, they seem invisible. This is problematic because my work will
be
> > continued by others, and allthough I will document everything I think it
> > should be possible to see the triggers and functions somehow...
> >
> > Secondly, I miss one final idea, when a delete is performed on the
> > b_news_unpublished table, I would like to set up a rule or procedure
that
> > sets the time value to null in b_news for each row that is affected by
the
> > delete. I understand that the OLD and NEW objects are accessible only
during
> > UPDATE or INSERT operations, so I can't quite see how to do this...
>
>
> OLD.* is available during TG_OP = ''DELETE'' in a trigger.

Indeed it is, I just didn't read the manual good enough :)

> There are sometimes some referential integrity problems on DELETE when
using
> triggers to do things on tables that have a RI relationship.  Like, if you
have
> a table that REFERENCES another table ON DELETE SET NULL, and there is a
DELETE
> proc on the referenced table that also does some other update on those
records,
> then the regular trigger might happen before the contraint trigger.  In
this
> case, an update would have a RI problem when it updates some attribute and
the
> contraint is checked again - it would fail since the referenced primary
key is
> deleted already but foreign key has not been SET NULL yet by the contraint
> trigger. I'm not sure what the rules are on the order of contraint trigger
and
> other trigger execution but sometimes the order isn't what you want and
then
> you get the problem on delete.  If it happens, like it happened to me, you
> might quit using foreign keys and just program your triggers as much as
you can
> to do the same checks that the contraint triggers are doing but by having
your
> own triggers do it, you have control of the order of how things happen on
> delete.

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
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.

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.

Again, I'm a postgre newbie, so there might be something essential I've
simply overlooked, but some clarification concerning this topic would be
nice.

Thanks

André Næss

[SQL] Just 'sql'?

[Itai Zukerman]

Hi, I have this:

CREATE FUNCTION queue_add() RETURNS OPAQUE AS 'BEGIN
  INSERT INTO queue ( count, price ) VALUES ( NEW.count, NEW.price );
  RETURN NEW;
END;' LANGUAGE 'plpgsql' ;

I can't figure out how to do this with LANGUAGE 'sql'.  Any ideas?

Thanks,
-itai

[SQL] Localization support

[Max Pyziur]

Greetings, 

Someone recently mentioned that the implementation of localization support in
PG7.0.x is different from pre-7.x.x releases.

My interest is in getting Ukrainian collation orders working in one of my
databases.

Any help in this regard would be appreciated greatly.



-- 
Max Pyziur BRAMA - Gateway Ukraine
[EMAIL PROTECTED]  http://www.brama.com/

Re: [SQL] Conditional rule?

[Robert B. Easter]

On Sat, 29 Jul 2000, André Næss 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