[SQL] trigger problem
Hi,
If I try this to run in a trigger function
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
perform dblink_exec('myconnect', 'insert into test (uid) values (' ||
quote_literal(NEW.pat_id) || ')');
return new;
perform dblink_disconnect('myconnect');'
I get the message
'ERROR: duplicate connection name
SQL state: 42710
Context: SQL statement "SELECT dblink_connect('myconnect','dbname=postgres
password=uzleuven')"
PL/pgSQL function "test_update_trigger" line 2 at perform'
This happens only in one of my two databases, anyone an idea?
Regards,
Jan
Re: [SQL] Trigger problem
On 6/8/2004 2:57 PM, Mike Rylander wrote: kasper wrote: Hi guys Im tryint to make a trigger that marks a tuble as changed whenever someone has updated it my table looks something like this create table myTable ( ... changed boolean; ) now ive been working on a trigger and a sp that looks like this, but it doesnt work... create function myFunction returns trigger as ' begin new.changed = true; The line above is using the SQL equaliy opperator, you want the assignment operator: := as in new.changed := true; PL/pgSQL accepts both. What's wrong is that it's an AFTER trigger, which is fired AFTER the new row is already stored on disk and thus cannot change it any more. Jan return new; end; ' language 'plpgsql'; create trigger myTrigger after update on lektioner for each row execute procedure myFunction(); the code compiles, runs, and doesnt whine about anything, but nothing changes... any ideas?? - Kasper -miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Trigger problem
On Tue, 8 Jun 2004, kasper wrote: > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); You want a before update trigger if you want to update the new row like that. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Trigger problem
kasper wrote: > Hi guys > > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; The line above is using the SQL equaliy opperator, you want the assignment operator: := as in new.changed := true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); > > > the code compiles, runs, and doesnt whine about anything, but nothing > changes... > > any ideas?? > > - Kasper -miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Trigger problem
Hi guys Im tryint to make a trigger that marks a tuble as changed whenever someone has updated it my table looks something like this create table myTable ( ... changed boolean; ) now ive been working on a trigger and a sp that looks like this, but it doesnt work... create function myFunction returns trigger as ' begin new.changed = true; return new; end; ' language 'plpgsql'; create trigger myTrigger after update on lektioner for each row execute procedure myFunction(); the code compiles, runs, and doesnt whine about anything, but nothing changes... any ideas?? - Kasper ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Trigger Problem
Mohammad Faisal <[EMAIL PROTECTED]> said: > hey all > > > I have created a function that is used in a trigger. > > -- > -- > > CREATE TRIGGER tr_insert_on_a AFTER INSERT OR UPDATE > ON A > FOR EACH ROW EXECUTE PROCEDURE fn_insert_on_a(); > > -- > -- > > CREATE function fn_insert_on_a() RETURNS OPAQUE AS > ' > BEGIN > INSERT INTO B name, a_ref VALUES > (NEW.name,NEW.id); > RETURN NEW; > END; > > ' LANGUAGE 'plpgsql'; > > > I am getting following error. > ERROR : > > Unrecognized language specified in a CREATE FUNCTION: > plpgsql. Recognized languages are sql,c,internal and > the created procedure languages. > > NOTE: > > I have studied in documenatation that only plpgsql > functions are written to be used in triggers. You need to issue: $ createlang plpgsql to create the plpgsql language on your database. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Trigger Problem
hey all I have created a function that is used in a trigger. -- -- CREATE TRIGGER tr_insert_on_a AFTER INSERT OR UPDATE ON A FOR EACH ROW EXECUTE PROCEDURE fn_insert_on_a(); -- -- CREATE function fn_insert_on_a() RETURNS OPAQUE AS ' BEGIN INSERT INTO B name, a_ref VALUES (NEW.name,NEW.id); RETURN NEW; END; ' LANGUAGE 'plpgsql'; I am getting following error. ERROR : Unrecognized language specified in a CREATE FUNCTION: plpgsql. Recognized languages are sql,c,internal and the created procedure languages. NOTE: I have studied in documenatation that only plpgsql functions are written to be used in triggers. any help in this regard. thanks in advance. Faisal __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
