Thread: A question about triggers...

Hello, i'm a portuguese university student doing my final project with a
postgresql database, apache and php3. I have to create an web interface
for doing operations on the pg database. Creates, inserts, drops, removes
etc.

Its nearly finished (i hope), but one of my problems is that i cant find
an example of creation of triggers or functions with sql. I need this to
do a (i hope) simple thing: create a trigger that implements external 
keys.

All the trigger has to do is, before inserting a tuple on a table T1, if
that table has an external key on field F1 (from field F2 on table T2),
see if the value of the field F1 on the tuple exists on table T2 (in the
field F2). If it does, continue with the insert, otherwise, dont insert.

I think i have the trigger part ok (although i cant test it without the
function):

create trigger trigg_ext_key_T1 before insert on T1 for each row
execute procedure func_ext_key_T1

I'm very unsure about the function part (still couldnt make it work):

CREATE FUNCTION func_ext_key_T1
(value_of_F1_in_tuple) RETURNS bool /* can i put the value as input? */

AS '   select * from T2 where F2 = value_of_F1_in_tuple
  /*     i probably cant do the following in pgsql, but i think it has to b     something similar  */
  EXCEPTION  WHEN NO_DATA_FOUND THEN  begin       return false  end;  '
LANGUAGE 'sql';

But how does the the trigger works? If the function returns (for example)
true he wont insert on the table? Is this how it should work? How is a
value passed from the insert that triggers the trigger to the function?

I'd be very grateful if someone could help me, even if its only showing me
an example of a function with sql that is used on a trigger. It may seem
i'm being lazy, asking instead of searching, but I've been to the postgres
site but couldnt find any examples, and there isnt anyone i know that has
worked with postgres before.

Bye.

    JPAjpapa@students.fct.unl.pt
'I love life. And i wanna live. That's what it's all about.'

when i dump out all dbspg_dumpall>dbs.out and try to put into a new
installation psql -e template1 <dbs.out it always fails when trying to
copy the actual data

usually producing errors that result in the \? listings and then a seq
fault

has anyone experienced this?

Clayton Cottingham <drfrog@smartt.com> writes:
> when i dump out all dbspg_dumpall>dbs.out and try to put into a new
> installation psql -e template1 <dbs.out it always fails when trying to
> copy the actual data
> usually producing errors that result in the \? listings and then a seq
> fault

There have been some reports that COPY in/out is not careful enough
about quoting control characters and so forth, so if you have text
fields containing control characters you might run into a problem
like the above.

As a quick workaround, try the pg_dump switch to dump data as INSERT
statements instead of with COPY.  It'll be a lot slower to load :(
but if it avoids the problem then this is probably the correct
diagnosis.

If you can track down exactly what data is making COPY misbehave,
we'd have a better shot at fixing the bug.
        regards, tom lane

Hi,

I have one table defined as follows:

CREATE TABLE bank ( .... incr FLOAT NOT NULL DEFAULT 0.0 , ....) ;

When I make query

SELECT * FROM bank WHERE incr = 11128.1;

it returns no result although at least one row satisfy this condition.
I can get result only with modified query

SELECT * FROM bank WHERE round(incr,1) = round(11128.1,1);

Is there some error in libc,OS or postgres? I have tried
to dump-destroy-create-restore whole database and
it looked good but after few hours I got into trouble again.

I run 6.5.1 version, compiled with gcc 2.95.1 (libc-2.1.2)
on Debian Linux 2.2.11.

Radek Kanovsky, rk@uh.cz

A question: does pg_dumpall save all the information of the database - I
think of grant rights, etc - or shall I restore them manually?

--------------------------------------------------------------------------------
Sebestyén Zoltán <szoli@netvisor.hu>    I'm believing that the Holy Spirit is                gonna allow the hand, and
thefoot, and
 
MAKE INSTALL NOT WAR            the mouth, just to begin to speak, and                                       to
minister,and to heal coordinated by                the head.
 

I use UNIX because reboots are for hardware upgrades.
                Kick me! Whip me!! Make me develop on AIX!!!

[Charset iso-8859-2 unsupported, filtering to ASCII...]
> A question: does pg_dumpall save all the information of the database - I
> think of grant rights, etc - or shall I restore them manually?

Saves them by running pg_dump.  In 6.5.*, no -z is needed.  In earlier
releases, you needed add -z option to save grants.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026