subject:"\[GENERAL\] Help with trigger"

Re: [GENERAL] Help with Trigger

2016-12-28 Thread Clifford Snow

Thank you for your suggestion which solved the problem. Much better
solution that what I was trying to accomplish. Much smaller table to query
since it only has one entry per user.

Clifford

On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver 
wrote:

> On 12/28/2016 07:06 PM, Clifford Snow wrote:
>
>> I'm trying to write a trigger (my first) to update another table if the
>> user_id is new. But I'm getting a index exception that the user_id
>>
>
> What is the actual error message?
>
> already exists. I'm picking up data from another feed which gives
>> provides me with changes to the main database.
>>
>> what I have is
>>
>> CREATE OR REPLACE FUNCTION add_new_user()
>> RETURNS TRIGGER AS
>> $BODY$
>> DECLARE
>> commits RECORD;
>> BEGIN
>> SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
>>
>
> In the above you are checking whether the changes table has the user_id
> and if does not then creating a new user in the user table below. Not sure
> how they are related, but from the description of the error it would seem
> they are not that tightly coupled. In other words just because the user_id
> does not exist in changes does not ensure it also absent from the table
> user. Off the top of head I would say the below might be a better query:
>
> SELECT INTO commits * FROM user WHERE user_id = NEW.user_id;
>
> Though it would help the debugging process if you showed the complete
> schema for both the changes and user tables.
>
>
> IF NOT FOUND
>> THEN
>> INSERT INTO user (user_name, user_id, change_id,
>> created_date)
>> VALUES(NEW.user_name, NEW.user_id,
>> NEW.change_id, NEW.created_date);
>> END IF;
>> RETURN NEW;
>> END;
>> $BODY$
>> LANGUAGE plpgsql;
>>
>> CREATE TRIGGER add_new_user_trigger
>> BEFORE INSERT ON changes
>> FOR EACH ROW
>> EXECUTE PROCEDURE add_new_user();
>>
>> I hoping for some recommendations on how to fix or at where I'm going
>> wrong.
>>
>> Thanks,
>> Clifford
>>
>>
>> --
>> @osm_seattle
>> osm_seattle.snowandsnow.us 
>> OpenStreetMap: Maps with a human touch
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

Re: [GENERAL] Help with Trigger

2016-12-28 Thread Adrian Klaver


On 12/28/2016 07:06 PM, Clifford Snow wrote:

I'm trying to write a trigger (my first) to update another table if the
user_id is new. But I'm getting a index exception that the user_id


What is the actual error message?


already exists. I'm picking up data from another feed which gives
provides me with changes to the main database.

what I have is

CREATE OR REPLACE FUNCTION add_new_user()
RETURNS TRIGGER AS
$BODY$
DECLARE
commits RECORD;
BEGIN
SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;


In the above you are checking whether the changes table has the user_id 
and if does not then creating a new user in the user table below. Not 
sure how they are related, but from the description of the error it 
would seem they are not that tightly coupled. In other words just 
because the user_id does not exist in changes does not ensure it also 
absent from the table user. Off the top of head I would say the below 
might be a better query:


SELECT INTO commits * FROM user WHERE user_id = NEW.user_id;

Though it would help the debugging process if you showed the complete 
schema for both the changes and user tables.




IF NOT FOUND
THEN
INSERT INTO user (user_name, user_id, change_id,
created_date)
VALUES(NEW.user_name, NEW.user_id,
NEW.change_id, NEW.created_date);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_new_user_trigger
BEFORE INSERT ON changes
FOR EACH ROW
EXECUTE PROCEDURE add_new_user();

I hoping for some recommendations on how to fix or at where I'm going wrong.

Thanks,
Clifford


--
@osm_seattle
osm_seattle.snowandsnow.us 
OpenStreetMap: Maps with a human touch



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Help with Trigger

2016-12-28 Thread Clifford Snow

I'm trying to write a trigger (my first) to update another table if the
user_id is new. But I'm getting a index exception that the user_id already
exists. I'm picking up data from another feed which gives provides me with
changes to the main database.

what I have is

CREATE OR REPLACE FUNCTION add_new_user()
RETURNS TRIGGER AS
$BODY$
DECLARE
commits RECORD;
BEGIN
SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
IF NOT FOUND
THEN
INSERT INTO user (user_name, user_id, change_id,
created_date)
VALUES(NEW.user_name, NEW.user_id, NEW.change_id,
NEW.created_date);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_new_user_trigger
BEFORE INSERT ON changes
FOR EACH ROW
EXECUTE PROCEDURE add_new_user();

I hoping for some recommendations on how to fix or at where I'm going wrong.

Thanks,
Clifford


-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

Re: [GENERAL] Help with trigger

2010-12-27 Thread Tom Lane

Michael Satterwhite  writes:
> On Monday, December 27, 2010 12:58:40 pm Guillaume Lelarge wrote:
>> Le 27/12/2010 18:57, Michael Satterwhite a écrit :
>>> I'm obviously missing something ... and probably something obvious. Why
>>> is date2 still null?
>> 
>> I'm not sure it'll help you. I copy/pasted your SQL script in my 9.0.2
>> release. Worked great.

> I'm running 8.4.2.

Well, as somebody already pointed out, the example you posted works
fine.  When I try it in 8.4.6, I get

# select * from test;
date1|date2
-+-
 2012-05-04 00:00:00 | 2012-04-27 00:00:00
(1 row)

I find it interesting that your quoted result is

# select * from test;
date1| date2 
-+---
 2012-04-27 00:00:00 | 
(1 row)

What it looks like from here is there's a typo in the actually-executing
version of the function, such that date1 not date2 is assigned the
week-old date value.  Perhaps "\df+ t_listing_startdate" would get
you started towards sorting it out.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with trigger

2010-12-27 Thread Guillaume Lelarge

Le 27/12/2010 22:16, Michael Satterwhite a écrit :
> On Monday, December 27, 2010 12:58:40 pm Guillaume Lelarge wrote:
>> Le 27/12/2010 18:57, Michael Satterwhite a écrit :
>>> I'm new to PostgreSQL, but have worked with other databases. I'm trying
>>> to write a trigger to default a timestamp column to a fixed interval
>>> before another. The test setup is as follows:
>>>
>>> create table test
>>> (   date1 timestamp,
>>>
>>> date2 timestamp
>>>
>>> );
>>>
>>> create or replace function t_listing_startdate() returns trigger as
>>> $t_listing_startdate$
>>>
>>> begin
>>> 
>>> if NEW.date2 is null then
>>> 
>>> NEW.date2 := NEW.date1 - interval '7 day';
>>> 
>>> end if;
>>> return NEW;
>>> 
>>> end;
>>>
>>> $t_listing_startdate$ LANGUAGE plpgsql;
>>>
>>> CREATE TRIGGER t_listing_startdate before insert or update on test
>>>
>>> for each row execute procedure t_listing_startdate();
>>>
>>> Insert into test(date1) values('May 4, 2012');
>>> INSERT 0 1
>>> test=# select * from test;
>>>
>>> date1| date2
>>>
>>> -+---
>>>
>>>  2012-04-27 00:00:00 |
>>>
>>> (1 row)
>>>
>>> I'm obviously missing something ... and probably something obvious. Why
>>> is date2 still null?
>>
>> I'm not sure it'll help you. I copy/pasted your SQL script in my 9.0.2
>> release. Worked great.
>>
>> What does \d says about your table? your trigger could be disabled.
> 
> I'm running 8.4.2.

I just tried in 8.4.6 and it works with your script.

> Here's the output
> 
> test=# \d test
>Table "public.test"
>  Column |Type | Modifiers 
> +-+---
>  date1  | timestamp without time zone | 
>  date2  | timestamp without time zone | 
> Triggers:
> t_listing_startdate BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE 
> PROCEDURE t_listing_startdate()
> 

So it isn't disabled (if it were, "Triggers:" would be replaced with
"Disabled triggers:").

Did you try on a new database of the same cluster?


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with trigger

2010-12-27 Thread Gary Chambers


Michael,


I'm new to PostgreSQL, but have worked with other databases. I'm trying
to write a trigger to default a timestamp column to a fixed interval
before another. The test setup is as follows:


Try this pg_dump of a working example:

CREATE FUNCTION t_listing_startdate() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
if new.d2 is null then
new.d2 := new.d1 - interval '7 day';
end if;
return new;
end;
$$;

CREATE TABLE t (
d1 timestamp without time zone,
d2 timestamp without time zone
);

CREATE TRIGGER t_listing_startdate
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW
EXECUTE PROCEDURE t_listing_startdate();

-- Gary Chambers

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with trigger

2010-12-27 Thread Richard Broersma

On Mon, Dec 27, 2010 at 1:14 PM, Michael Satterwhite
 wrote:

> I've *GOT* to be missing something in this post. You start by quoting the
> "Create Trigger" that attaches the trigger to the table. Then you tell me that
> I've got to do what you showed that I did.


Oops, your right, I miss-read your post.  Sorry.


-- 
Regards,
Richard Broersma Jr.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with trigger

2010-12-27 Thread Richard Broersma

On Mon, Dec 27, 2010 at 9:57 AM, Michael Satterwhite
 wrote:

> CREATE TRIGGER t_listing_startdate before insert or update on test
>        for each row execute procedure t_listing_startdate();

Now that you've created a trigger function, you need to attached to your table:

http://www.postgresql.org/docs/9.0/interactive/sql-createtrigger.html


-- 
Regards,
Richard Broersma Jr.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with trigger

2010-12-27 Thread Guillaume Lelarge

Le 27/12/2010 18:57, Michael Satterwhite a écrit :
> I'm new to PostgreSQL, but have worked with other databases. I'm trying to 
> write a trigger to default a timestamp column to a fixed interval before 
> another. The test setup is as follows:
> 
> create table test
> ( date1 timestamp,
>   date2 timestamp
> );
> 
> create or replace function t_listing_startdate() returns trigger as 
> $t_listing_startdate$
>   begin
>   if NEW.date2 is null then
>   NEW.date2 := NEW.date1 - interval '7 day';
>   end if;
>   return NEW;
>   end;
> $t_listing_startdate$ LANGUAGE plpgsql;
> 
> CREATE TRIGGER t_listing_startdate before insert or update on test
>   for each row execute procedure t_listing_startdate();
> 
> Insert into test(date1) values('May 4, 2012');
> INSERT 0 1
> test=# select * from test;
> date1| date2 
> -+---
>  2012-04-27 00:00:00 | 
> (1 row)
> 
> I'm obviously missing something ... and probably something obvious. Why is 
> date2 still null?
> 

I'm not sure it'll help you. I copy/pasted your SQL script in my 9.0.2
release. Worked great.

What does \d says about your table? your trigger could be disabled.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Help with trigger

2010-12-27 Thread Gary Chambers


Michael,


I'm new to PostgreSQL, but have worked with other databases. I'm trying to
write a trigger to default a timestamp column to a fixed interval before
another. The test setup is as follows:

create table test
(   date1 timestamp,
date2 timestamp
);

create or replace function t_listing_startdate() returns trigger as
$t_listing_startdate$
begin
if NEW.date2 is null then
NEW.date2 := NEW.date1 - interval '7 day';
end if;
return NEW;
end;
$t_listing_startdate$ LANGUAGE plpgsql;

CREATE TRIGGER t_listing_startdate before insert or update on test
for each row execute procedure t_listing_startdate();

Insert into test(date1) values('May 4, 2012');
INSERT 0 1
test=# select * from test;
   date1| date2
-+---
2012-04-27 00:00:00 |
(1 row)


With the exception of abbreviating the table (t) and column names (d1 and
d2), your example as submitted works for me (8.4.5, MacOSX).  What version
of Pg are you using and on which platform?

-- Gary Chambers

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] help with trigger

2004-08-27 Thread Dino Vliet

Maybe I've missed it but can someone plese help me
with this?

Brgds and thanks in advance,

--- Dino Vliet <[EMAIL PROTECTED]> wrote:

> Hi folks,
> I'm new to PostgreSQL and am busy tring to work with
> it. Of pl/pgsql I know even less and that's the part
> I
> have a question on right now. I have this nice
> example
> to get me started with
> pl/pgsql...
> 
> I have a table with the schedule of a service my
> sport
> team wants to offer:
> 
> Table Schedule
> name   length   startdate enddateday
> soccer   4  01-sep-2004  30-sep-2004  
> Mon
> tennis   4  01-sep-2004  30-sep-2004  
> Wed
> 
> This means, that I want to offer soccer weekly, for
> a
> total of 4 weeks as from 1st sep till 30 sep on
> Mondays!
> 
> Now, what I do want is a second table that creates
> all
> the dates on which there will be soccer training if
> I
> finish entering this record in table Schedule. So,
> then the other table, called ScheduledDates has the
> follwing records:
> 
> Table ScheduleDates
> nameoccurrence  date
> soccer   1  06-sep-2004
> soccer   2  13-sep-2004
> soccer   3  20-sep-2004
> soccer   4  27-sep-2004
> 
> Can someone help me with writing this as a trigger
> in
> pl/pgsql?
> 
> Another variant is that the trigger starts when the
> table Schedule is populated, but it not just
> automatically creates the 4 records as described
> above, but because the date 20-sep-2004 is a
> national
> holiday and is in table Exceptions, the sheme just
> shifts one up and the occurrence 4 becomes
> occurrence
> 3 and the last schedule date becomes monday the 4th
> of
> october!
> 
> I hope you can help me with this one, will put me
> way
> up the learning curve of pl/pgsql
> 
> 
>   
>   
> __
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail 
> 




__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] help with trigger

2004-08-25 Thread Dino Vliet

Hi folks,
I'm new to PostgreSQL and am busy tring to work with
it. Of pl/pgsql I know even less and that's the part I
have a question on right now. I have this nice example
to get me started with
pl/pgsql...

I have a table with the schedule of a service my sport
team wants to offer:

Table Schedule
name   length   startdate enddateday
soccer   4  01-sep-2004  30-sep-2004   Mon
tennis   4  01-sep-2004  30-sep-2004   Wed

This means, that I want to offer soccer weekly, for a
total of 4 weeks as from 1st sep till 30 sep on
Mondays!

Now, what I do want is a second table that creates all
the dates on which there will be soccer training if I
finish entering this record in table Schedule. So,
then the other table, called ScheduledDates has the
follwing records:

Table ScheduleDates
nameoccurrence  date
soccer   1  06-sep-2004
soccer   2  13-sep-2004
soccer   3  20-sep-2004
soccer   4  27-sep-2004

Can someone help me with writing this as a trigger in
pl/pgsql?

Another variant is that the trigger starts when the
table Schedule is populated, but it not just
automatically creates the 4 records as described
above, but because the date 20-sep-2004 is a national
holiday and is in table Exceptions, the sheme just
shifts one up and the occurrence 4 becomes occurrence
3 and the last schedule date becomes monday the 4th of
october!

I hope you can help me with this one, will put me way
up the learning curve of pl/pgsql




__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Help with Trigger

Re: [GENERAL] Help with Trigger

[GENERAL] Help with Trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] Help with trigger

Re: [GENERAL] help with trigger

[GENERAL] help with trigger

12 matches

Site Navigation

Mail list logo

Footer information