pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger problem
10+ messages / 9 participants
[nested] [flat]
* Trigger problem
@ 2001-03-17 05:22 jreniz <jreniz@tutopia.com>
0 siblings, 1 reply; 10+ messages in thread
From: jreniz @ 2001-03-17 05:22 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org.pgsql-admin
Hi friends!
I'm working in a trigger and I need to put the result
of a query into a variable.
That's very easy- apparently!
The query has a aggregate function like this:
select sum(field) into variable ...
and I'm sure that field and variable are int4 type.
So, when I run this trigger there is a mistake:
''there is no operator '=$' for types 'int4' and 'int4'
you will either have to retype this query using an
explicit cast, or you will have to define the operator
using CREATE OPERATOR''
what's meaning this? and
how can I assign the result of aggregate function into a
variable?
(My system is 6.5.3)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger problem
@ 2001-03-18 20:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: jreniz <jreniz@tutopia.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-03-18 20:37 UTC (permalink / raw)
To: jreniz <jreniz@tutopia.com>; +Cc: pgsql-hackers@postgreSQL.org
jreniz <jreniz@tutopia.com> writes:
> So, when I run this trigger there is a mistake:
> ''there is no operator '=$' for types 'int4' and 'int4'
> (My system is 6.5.3)
This is an old bug. Update to 7.0.3.
It might work to add spaces around the '=' signs in your trigger
function, but an update would be a good idea anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Trigger Problem
@ 2001-09-20 07:58 Mohammad Faisal <faisalchouhan@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Mohammad Faisal @ 2001-09-20 07:58 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger Problem
@ 2001-09-20 10:56 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 10+ messages in thread
From: Andreas Joseph Krogh @ 2001-09-20 10:56 UTC (permalink / raw)
To: pgsql-sql
Mohammad Faisal <faisalchouhan@yahoo.com> 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 <db_name>
to create the plpgsql language on your database.
--
Andreas Joseph Krogh <andreak@officenet.no>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger problem
@ 2004-06-08 18:57 Mike Rylander <miker@purplefrog.com>
parent: kasper <no@spam.com>
1 sibling, 1 reply; 10+ messages in thread
From: Mike Rylander @ 2004-06-08 18:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Trigger problem
@ 2004-06-08 19:38 kasper <no@spam.com>
0 siblings, 2 replies; 10+ messages in thread
From: kasper @ 2004-06-08 19:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger problem
@ 2004-06-09 20:30 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: kasper <no@spam.com>
1 sibling, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2004-06-09 20:30 UTC (permalink / raw)
To: kasper <no@spam.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Trigger problem
@ 2004-06-10 01:44 Jan Wieck <JanWieck@Yahoo.com>
parent: Mike Rylander <miker@purplefrog.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jan Wieck @ 2004-06-10 01:44 UTC (permalink / raw)
To: miker@purplefrog.com; +Cc: pgsql-sql
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. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 10+ messages in thread
* trigger problem
@ 2009-08-06 09:09 Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 0 replies; 10+ messages in thread
From: Jan Verheyden @ 2009-08-06 09:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* FW: trigger problem
@ 2009-08-06 13:56 Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
0 siblings, 0 replies; 10+ messages in thread
From: Jan Verheyden @ 2009-08-06 13:56 UTC (permalink / raw)
To: pgsql-sql
Hi,
I keep looking for myself and tried as well the following code:
'perform dblink_connect('myconnect','dbname=postgres password=uzleuven');
create view remote as
select *
from dblink('myconnect','select uid from test')
as t1(pat_id text);
perform * from remote where pat_id like '|| query_literal(NEW.pat_id) ||';
return new;
perform dblink_disconnect('myconnect');'
And again I get the same error message as below..
Is this because it tries to make connection for each row in the column??
Thanks,
Jan
From: Jan Verheyden
Sent: Thursday, August 06, 2009 11:09 AM
To: 'pgsql-sql@postgresql.org'
Subject: 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', 'update test set uploaded = 1 where uid =' || 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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2009-08-06 13:56 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-17 05:22 Trigger problem jreniz <jreniz@tutopia.com>
2001-03-18 20:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-09-20 07:58 Trigger Problem Mohammad Faisal <faisalchouhan@yahoo.com>
2001-09-20 10:56 Re: Trigger Problem Andreas Joseph Krogh <andreak@officenet.no>
2004-06-08 19:38 Trigger problem kasper <no@spam.com>
2004-06-08 18:57 ` Mike Rylander <miker@purplefrog.com>
2004-06-10 01:44 ` Jan Wieck <JanWieck@Yahoo.com>
2004-06-09 20:30 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2009-08-06 09:09 trigger problem Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
2009-08-06 13:56 FW: trigger problem Jan Verheyden <jan.verheyden@uz.kuleuven.ac.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox