[SQL] row not deleted but updated (trigger?)
I've:
create or replace function FT1IDX_catalog_brands_update() returns
trigger as $$
begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
Authors, '')
where BrandID=old.BrandID;
else
if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
Name, Authors, new.Name)
where BrandID=new.BrandID;
end if;
end if;
return new;
end $$ language plpgsql volatile;
create trigger FT1IDX_catalog_brands_update_trigger before update or
delete on catalog_brands for each row execute procedure
FT1IDX_catalog_brands_update();
I do something
update catalog_brands set name='zzz' where brandid=1234;
1 row get updated.
When I do
delete from catalog_brands where brandid=1234;
no row get deleted and no error get reported.
what did I miss?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] row not deleted but updated (trigger?)
On Fri, 27 Feb 2009 12:56:06 +0100
Ivan Sergio Borgonovo wrote:
> I've:
>
> create or replace function FT1IDX_catalog_brands_update() returns
> trigger as $$
> begin
> if(TG_OP='DELETE') then
> update catalog_items set
> FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
> Name, Authors, '')
> where BrandID=old.BrandID;
> else
> if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
> update catalog_items set
> FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
> Name, Authors, new.Name)
> where BrandID=new.BrandID;
> end if;
> end if;
> return new;
> end $$ language plpgsql volatile;
umpfs...
on delete new is null... so no action take place.
modified to return old on delete and new for the rest.
Sorry for the noise.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] row not deleted but updated (trigger?)
"delete" trigger should return "old". In your code you return "new" for both: "update" and "delete" Igor -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Ivan Sergio Borgonovo Sent: Friday, February 27, 2009 6:56 AM To: [email protected] Subject: [SQL] row not deleted but updated (trigger?) I've: create or replace function FT1IDX_catalog_brands_update() returns trigger as $$ begin if(TG_OP='DELETE') then update catalog_items set FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, '') where BrandID=old.BrandID; else if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then update catalog_items set FT1IDX=GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, new.Name) where BrandID=new.BrandID; end if; end if; return new; end $$ language plpgsql volatile; create trigger FT1IDX_catalog_brands_update_trigger before update or delete on catalog_brands for each row execute procedure FT1IDX_catalog_brands_update(); I do something update catalog_brands set name='zzz' where brandid=1234; 1 row get updated. When I do delete from catalog_brands where brandid=1234; no row get deleted and no error get reported. what did I miss? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] counts of groupings by date year-month
Hi, I have a table called temp access_date | active | status -++ 2009-02-01 | t | 15 2009-02-01 | f | 16 2009-02-02 | f | 17 2009-02-01 | t | 17 2009-02-02 | f | 21 2009-01-01 | t | 20 2009-01-01 | t | 21 2009-01-01 | f | 21 What I want is to be able to get counts of active by year-month. So the output would be like: year_month | count +--- 200901 | 3 200902 | 5 I tried something like SELECT to_char(access_date, 'MM') as year_month, count(year_month) FROM temp GROUP BY year_month ORDER BY year_month; but I'm unable to execute this query because the column "year_month" doesn't exist in temp table. Is it possible to get counts by year_month? Thanks for your help in advance, CC -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] counts of groupings by date year-month
On Fri, Feb 27, 2009 at 2:02 PM, Carol Cheung
wrote:
> Hi,
> I have a table called temp
>
> access_date | active | status
> -++
> 2009-02-01 | t | 15
> 2009-02-01 | f | 16
> 2009-02-02 | f | 17
> 2009-02-01 | t | 17
> 2009-02-02 | f | 21
> 2009-01-01 | t | 20
> 2009-01-01 | t | 21
> 2009-01-01 | f | 21
>
>
> What I want is to be able to get counts of active by year-month. So the
> output would be like:
>
> year_month | count
> +---
> 200901 | 3
> 200902 | 5
>
> I tried something like
> SELECT to_char(access_date, 'MM') as year_month, count(year_month) FROM
> temp GROUP BY year_month ORDER BY year_month;
>
> but I'm unable to execute this query because the column "year_month" doesn't
> exist in temp table.
Try date_trunc:
select date_trunc('day',timestamp), count(*) from table where active
is true group by date_trunc('day',timestamp) order by
date_trunc('day',timestamp);
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] counts of groupings by date year-month
Carol Cheung writes: > I tried something like > SELECT to_char(access_date, 'MM') as year_month, count(year_month) > FROM temp GROUP BY year_month ORDER BY year_month; The only problem in what you wrote was the illegal cross-reference from one output column to another. Just use count(*) instead. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] counts of groupings by date year-month
Carol Cheung wrote: > Hi, > I have a table called temp > > access_date | active | status > -++ > 2009-02-01 | t | 15 > 2009-02-01 | f | 16 > 2009-02-02 | f | 17 > 2009-02-01 | t | 17 > 2009-02-02 | f | 21 > 2009-01-01 | t | 20 > 2009-01-01 | t | 21 > 2009-01-01 | f | 21 > > > What I want is to be able to get counts of active by year-month. So the > output would be like: > > year_month | count > +--- > 200901 | 3 > 200902 | 5 > > I tried something like > SELECT to_char(access_date, 'MM') as year_month, count(year_month) > FROM temp GROUP BY year_month ORDER BY year_month; > > but I'm unable to execute this query because the column "year_month" > doesn't exist in temp table. > > Is it possible to get counts by year_month? change count(year_month) to count(1), untested. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
