date:20120831

On Î Î±Ï 31 ÎÏÎ³ 2012 09:19:26 Chris Angelico wrote:
 On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
 ach...@smadev.internal.net wrote:
  I have found useful the use of variable assignment in psql, e.g.
 
  #!/bin/sh
 
  # lets say you have some var with a value, or even populate some var with a 
  value from
  # psql as shown below
  somevar=`psql -P pager=off -q -t -c SELECT foo from bar | head -1 | sed 
  -e 's/ //g'`
 
  # now use that variable in psql, (what you want to achieve), but in more 
  tight manner
  # than simple shell substitution (see -v switch and : notation)
 
  psql -P pager=off -q -v somevar=$somevar -c select foo2 from bar2 where 
  var=:somevar
 
 At this point, I have to ask: Why not switch to a language with actual
 Postgres bindings? Try Python, or Pike, or something; I'm sure it's
 going to be easier than doing everything through shell scripts.
 

or perl, or php, or java, etc...
actually we switched to java some 11 years ago to build our infrastructure,
but occasionally (or not so occasionally, but rather being part of the 
architecture)
still sh/bach/tcsh/perl are heavily used and have their place.
Its all about taste/preference and not easily jumping into overkill mode.

OTOH the OP asked for doing exactly what -v (--set) var assignments are 
supposed to do.
(lift shell substitution ambiguities about escaping and var expansion)

 ChrisA
 
 
 
-
Achilleas Mantzios
IT DEPT


-- 
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] psql unix env variables

On Παρ 31 Αυγ 2012 09:37:05 Craig Ringer wrote:
 On 08/30/2012 02:42 PM, Achilleas Mantzios wrote:
  I have found useful the use of variable assignment in psql, e.g.
 
 If you're going to to that, why not drive psql as a coprocess:

because it is completely irrelevant with what the OP asked for.

 
 http://stackoverflow.com/a/8305578/398670
 
 or if at all possible, use a language with sane PostgreSQL bindings.
 
 --
 Craig Ringer
 
 
 
-
Achilleas Mantzios
IT DEPT


-- 
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] psql unix env variables

On Î Î±Ï 31 ÎÏÎ³ 2012 09:19:26 Chris Angelico wrote:
 On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
 ach...@smadev.internal.net wrote:
  I have found useful the use of variable assignment in psql, e.g.
 
  #!/bin/sh
 
  # lets say you have some var with a value, or even populate some var with a 
  value from
  # psql as shown below
  somevar=`psql -P pager=off -q -t -c SELECT foo from bar | head -1 | sed 
  -e 's/ //g'`
 
  # now use that variable in psql, (what you want to achieve), but in more 
  tight manner
  # than simple shell substitution (see -v switch and : notation)
 
  psql -P pager=off -q -v somevar=$somevar -c select foo2 from bar2 where 
  var=:somevar
 
 At this point, I have to ask: Why not switch to a language with actual
 Postgres bindings? Try Python, or Pike, or something; I'm sure it's
 going to be easier than doing everything through shell scripts.
 

or perl, or php, or java, etc...
actually we switched to java some 11 years ago to build our infrastructure,
but occasionally (or not so occasionally, but rather being part of the 
architecture)
still sh/bach/tcsh/perl are heavily used and have their place.
Its all about taste/preference and not easily jumping into overkill mode.

OTOH the OP asked for doing exactly what -v (--set) var assignments are 
supposed to do.
(lift shell substitution ambiguities about escaping and var expansion)

 ChrisA
 
 
 
-
Achilleas Mantzios
IT DEPT


-- 
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] psql unix env variables

On Παρ 31 Αυγ 2012 09:37:05 Craig Ringer wrote:
 On 08/30/2012 02:42 PM, Achilleas Mantzios wrote:
  I have found useful the use of variable assignment in psql, e.g.
 
 If you're going to to that, why not drive psql as a coprocess:

because it is completely irrelevant with what the OP asked for.

 
 http://stackoverflow.com/a/8305578/398670
 
 or if at all possible, use a language with sane PostgreSQL bindings.
 
 --
 Craig Ringer
 
 
 
-
Achilleas Mantzios
IT DEPT


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

[GENERAL] Postgresql Error ask for password

2012-08-31 Thread José Pedro Santos


Dear all, 

Im trying to install one application in my Linux but when I try to use the 
expression SU postgresql it ask for password, what is the password? I try my 
root password but dont work. 

Thanks for your time

Ciao 
Jose Santos

Re: [GENERAL] Postgresql Error ask for password

2012-08-31 Thread Adrian Klaver


On 08/31/2012 07:29 AM, José Pedro Santos wrote:

Dear all,

Im trying to install one application in my Linux but when I try to use
the expression SU postgresql it ask for password, what is the password?
I try my root password but dont work.


More information is needed.

What variety of Linux are you using and what version?

How did you install Postgres?:
From source
Using package manager
Using one click installer.





Thanks for your time

Ciao
Jose Santos



--
Adrian Klaver
adrian.kla...@gmail.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] Postgresql Error ask for password

2012-08-31 Thread Rainer Pruy


On 31.08.2012 16:29, José Pedro Santos wrote:
 Dear all,

 Im trying to install one application in my Linux but when I try to use
 the expression SU postgresql it ask for password, what is the
 password? I try my root password but dont work.
Sorry, you are a bit terse on the context of this.

Basically, there might not be a true password for user postgresql (if
your installation is even using that user id, most use postgres or
pgsql)
If you are in the process of installing some application and that
requests you to enter su postgresql, than likely this procedure is
expecting
you being root. Then su will succeed (if user is known at all - see
above). Nevertheless, most distributions of Linux would prefer you to
do a variant of sudo and avoid su completely. So, if you are running
all this as a different user (and not happen to be postgresql user
already),
become root and proceed with su as told.

However, may be you step back and try to make sense out of what you are
supposed to do identify (and overcome) those inaccuracies, that seem
to exists with your instructions.

Or are you just poking around based on experience that do not perfectly
fit with the current environment?


The symptoms you depict match a variety of causes (and problems),
thus it really is difficult to give proper advice

Rainer

 Thanks for your time

 Ciao
 Jose Santos

Re: [GENERAL] Postgresql Error ask for password

2012-08-31 Thread Albe Laurenz

Pedro Santos wrote:
 Im trying to install one application in my Linux but when I try to use the 
 expression SU postgresql it
 ask for password, what is the password? I try my root password but dont work.

It may be that you never set that password.

Try grep postgresql /etc/shadow as root user.
If the second field is !!, there is no password set.

You can either become root first, then you need no password
for su postgresql, or you have to set a password.

You can change the password as user root with
passwd postgresql.

Yours,
Laurenz Albe


-- 
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] Postgresql Error ask for password

2012-08-31 Thread José Pedro Santos

I install the FGS distribution of MapServer, after, the plugin Postgres - 
Server. When I go to root and put in bash SU also ask for password...

Thanks

 Date: Fri, 31 Aug 2012 07:35:57 -0700
 From: adrian.kla...@gmail.com
 To: zpsant...@hotmail.com
 CC: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Postgresql Error ask for password

 On 08/31/2012 07:29 AM, José Pedro Santos wrote:
  Dear all,

  Im trying to install one application in my Linux but when I try to use
  the expression SU postgresql it ask for password, what is the password?
  I try my root password but dont work.

 More information is needed.

 What variety of Linux are you using and what version?

 How did you install Postgres?:
  From source
 Using package manager
 Using one click installer.

  Thanks for your time

  Ciao
  Jose Santos

 -- 
 Adrian Klaver
 adrian.kla...@gmail.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] Postgresql Error ask for password

2012-08-31 Thread Adrian Klaver

On 08/31/2012 08:03 AM, José Pedro Santos wrote:
 I install the FGS distribution of MapServer, after, the plugin Postgres 
 - Server. When I go to root and put in bash SU also ask for password...


Here is an answer from the FGS mailing list:

http://lists.maptools.org/pipermail/foss-gis-suite/2010-February/000893.html

For future reference the mailing list is:

http://lists.maptools.org/mailman/listinfo/foss-gis-suite/

 
 Thanks
 



-- 
Adrian Klaver
adrian.kla...@gmail.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] Postgresql Error ask for password

2012-08-31 Thread José Pedro Santos


Ok, I will try that. 

Thanks to all for the information.

Best Regards

José Santos

 Date: Fri, 31 Aug 2012 08:08:06 -0700
 From: adrian.kla...@gmail.com
 To: zpsant...@hotmail.com
 CC: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Postgresql Error ask for password
 
 On 08/31/2012 08:03 AM, José Pedro Santos wrote:
  I install the FGS distribution of MapServer, after, the plugin Postgres 
  - Server. When I go to root and put in bash SU also ask for password...
 
 
 Here is an answer from the FGS mailing list:
 
 http://lists.maptools.org/pipermail/foss-gis-suite/2010-February/000893.html
 
 For future reference the mailing list is:
 
 http://lists.maptools.org/mailman/listinfo/foss-gis-suite/
 
  
  Thanks
  
 
 
 
 -- 
 Adrian Klaver
 adrian.kla...@gmail.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] RE: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?‏

2012-08-31 Thread John Lumby

___
 From: pavan.deola...@gmail.com 
 Date: Fri, 31 Aug 2012 11:09:42 +0530 
 Subject: Re: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails 
 the WHERE predicate ?‏ 

 On Thu, Aug 30, 2012 at 6:31 PM, John Lumby  
 johnlu...@hotmail.commailto:johnlu...@hotmail.com wrote: 

 I would like to use an UPDATE RULE to modify the action performed 
 when any UPDATE is attempted on a certain table, 
 *including* an UPDATE which would fail because of no rows matching the WHERE. 

 You did not mention why you need such a facility, but AFAICS RULEs will  
 only be applied on the qualifying rows. So as you rightly figured out,  
 you won't see them firing unless there are any qualifying rows. Is this  
 not something you can achieve via statement-level triggers though ? 

Thanks Pavan;   what I need to do is to intercept certain UPDATE statements
which would fail because of no rows matching the WHERE,  and instead
issue a different UPDATE which will not fail but will have the same intended 
effect.

The context is a java application which uses hibernate for object-relational 
mapping,
and the specific case is hibernate optimistic locking.

hibernate provides a way of serializing all INS/UPD/DEL operations performed
under any single parent row in a table that has a heirarchy defined by a
kind of self-referencing referential constraint,   that is, 
each row has a parent_id column pointing to some other row.

It is possible to tell hibernate to serialize INS/UPD/DELon any particular 
table.
hibernate then uses another column named version to do the serialization -
using a sequence like so (for example of an INS):

 1  .    SELECT parent entity of entity to be INSerted,
                 by specifying WHERE id = parent_id
                                   and note its version   -  let's say version 
= V

 2  .    INSERT the new entity with version set to 0

 3  .    UPDATE the parent entity  :   set version = (V+1)
                                 WHERE id= parent_id  AND version = V
    throw exception and ROLLBACK the INSERT if this UPDATE failed
    (it will fail if another thread had performed another
                intervening INSERT and updated parent's version)

Now,   our problem is that control of this optimistic locking behaviour is per 
table,
whereas we ideally want it to operate at the level of object type within table.
That is,  in certain well-defined cases,   we do not want this serialization to 
be done.
My idea was to intercept the UPDATE in these cases and change the UPDATE into
   UPDATE the parent entity  :   set version = (OLD.version+1)

                                 WHERE id= parent_id
so the parent's version would be set correctly but concurrent inserts would be 
permitted.

So now to your suggestion of a trigger  -    
Yes,   I think it can be invoked in the case in question,  but only if it is 
defined as
a BEFORE statement trigger,  not an INSTEAD OF trigger,   and then it cannot
prevent the failing UPDATE from being done after it (trigger) has run.
We would really need an INSTEAD OF statement-level trigger but there is no such 
capability.

RULEs seem to be more general than triggers and I didn't see anything quite so 
clear-cut
in the documentation to imply it can't be done,   other than the notes I quoted 
earlier
from chapter 38.3.1. How Update Rules Work
about the query trees and that the original query's qualification is always 
present.

Also,  when I ran the test of the RULE,  I thought it was significant that psql 
showed the
name of my RULE function as though it was somehow being invoked :
update updatable set version = 2 where id = 1 and version = 1
 optlock_control 
-
(0 rows)

UPDATE 0

 Thanks, 
 Pavan 

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

[GENERAL] Tigger after delete with plpgsql

2012-08-31 Thread Fellipe Henrique

Hello, I`m try to use this code for my After Delete trigger:

  delete
  from MOVIMENTO
  where (IDEMPRESA = OLD.idempresa) and
(upper(TABELA) = 'NFENTRADA_ITEM') and
(CODIGO = OLD.idempresa_item);

But, when execute. show me error.. that`s says

old is not initialize (sorry,but I dont know how exactly error msy
because in my SO, give in portuguese)..

my question is:  how can I use OLd and NEW in posgre?

Best Regards,


T.·.F.·.A.·. S+F

*Fellipe Henrique P. Soares*

*Life is a game without Tutorial*

*http://fellipeh.eti.br*

Re: [GENERAL] Refreshing functional index

2012-08-31 Thread Grzegorz Tańczyk


Hello,

W dniu 2012-08-29 23:06, Merlin Moncure pisze:
Well, the only reason what you're trying to do works at all is because 
the database isn't stricter about double checking to see if your stuff 
is IMMUTABLE: it isn't, so of course it doesn't work. How about a 
trigger on the child table that updates an indexed column on parent? 
merlin 


According to docs:
AnIMMUTABLEfunction cannot modify the database and is guaranteed to 
return the same results given the same arguments forever.


My tables look like this:
CREATE TABLE groups (
  id serial PRIMARY KEY,
  last_item integer REFERENCES items
) WITHOUT OIDS;

CREATE TABLE items (
  id serial PRIMARY KEY,
  group integer NOT NULL REFERENCES groups,
  ts timestamp DEFAULT now()
) WITHOUT OIDS;

The index:
CREATE INDEX groups_last_ts
  ON groups
  USING btree
  (items_ts(last_post));

Plpgsql function items_ts returns timestamp for given item, which will 
never change(that's my assumption), so in fact according to definition 
IT IS immutable fuction.


Unfortunately, whenever I update last_item column in groups, I get wrong 
results, so I query like this:


SELECT * FROM groups WHERE items_ts(last_item)  now() - interval '1 week'

returns outdated results

I do realize about other ways for solving this problem, however I would 
prefer if it worked in the way described above.


Thanks!

--
Regards,
  Grzegorz

Re: [GENERAL] Tigger after delete with plpgsql

On Friday, August 31, 2012 02:10:47 PM Fellipe Henrique wrote:
 Hello, I`m try to use this code for my After Delete trigger:
 
   delete
   from MOVIMENTO
   where (IDEMPRESA = OLD.idempresa) and
 (upper(TABELA) = 'NFENTRADA_ITEM') and
 (CODIGO = OLD.idempresa_item);
 
 But, when execute. show me error.. that`s says
 
 old is not initialize (sorry,but I dont know how exactly error msy
 because in my SO, give in portuguese)..
 
 my question is:  how can I use OLd and NEW in posgre?
 

Just like that. Which implies something else is wrong. Please post the whole 
trigger function and a \d of the table where this trigger is used, and the SQL  
that you are executing that results in the error.



-- 
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] Tigger after delete with plpgsql

2012-08-31 Thread Tulio


  
  
Hi,

You're using a trigger AFTER, in your case could be BEFORE?
Cause when you use AFTER the var OLD not exists...
I don't know if I understand exactly what you'll do to ativate this
trigger..
but I think maybe is it..

try..


Em 31/08/2012 14:10, Fellipe Henrique escreveu:
Hello, I`m try to use this code for my After Delete
  trigger:
  
  
  
 delete
 from MOVIMENTO
 where (IDEMPRESA = OLD.idempresa) and
(upper(TABELA) = 'NFENTRADA_ITEM') and
(CODIGO = OLD.idempresa_item);


But, when execute. show me error.. that`s says


"old is not initialize" (sorry,but I dont know how exactly
  error msy because in my SO, give in portuguese)..


my question is: how can I use OLd and NEW in posgre?


Best Regards,




T..F..A.. S+F
Fellipe
  Henrique P. Soares
  
"Life
  is a game without Tutorial"
http://fellipeh.eti.br

Re: [GENERAL] Tigger after delete with plpgsql

2012-08-31 Thread Fellipe Henrique

Hi,

I`m using Before Delete..


CREATE TRIGGER nfentrada_item_tr1
  BEFORE DELETE
  ON public.nfentrada_item FOR EACH ROW
  EXECUTE PROCEDURE public.nfentrada_item_ad0();

here is my nfentrada_item_ad0();

delete
  from MOVIMENTO
  where (IDEMPRESA = OLD.idempresa) and
(upper(TABELA) = 'NFENTRADA_ITEM') and
(CODIGO = OLD.idnfentrada_item);
  return old;

I just want to delete all row in my MOVIMENTO table with these
conditionals..

Thanks,


T.·.F.·.A.·. S+F

*Fellipe Henrique P. Soares*

*Life is a game without Tutorial*

*http://fellipeh.eti.br*



2012/8/31 Tulio tu...@informidia.com.br

  Hi,

 You're using a trigger AFTER, in your case could be BEFORE?
 Cause when you use AFTER the var OLD not exists...
 I don't know if I understand exactly what you'll do to ativate this
 trigger..
 but I think maybe is it..

 try..


 Em 31/08/2012 14:10, Fellipe Henrique escreveu:

 Hello, I`m try to use this code for my After Delete trigger:

delete
   from MOVIMENTO
   where (IDEMPRESA = OLD.idempresa) and
 (upper(TABELA) = 'NFENTRADA_ITEM') and
 (CODIGO = OLD.idempresa_item);

  But, when execute. show me error.. that`s says

  old is not initialize (sorry,but I dont know how exactly error msy
 because in my SO, give in portuguese)..

  my question is:  how can I use OLd and NEW in posgre?

  Best Regards,


  T.·.F.·.A.·. S+F

 *Fellipe Henrique P. Soares*

 *Life is a game without Tutorial*

 *http://fellipeh.eti.br*

Re: [GENERAL] Refreshing functional index

Grzegorz
Hello,

W dniu 2012-08-29 23:06, Merlin Moncure pisze:
Well, the only reason what you're trying to do works at all is because the 
database isn't stricter about double checking to see if your stuff is 
IMMUTABLE: it isn't, so of course it doesn't work. How about a trigger on the 
child table that updates an indexed column on parent? merlin 

According to docs:
An IMMUTABLE function cannot modify the database and is guaranteed to return 
the same results given the same arguments forever.

My tables look like this:
CREATE TABLE groups (
  id serial PRIMARY KEY,
  last_item integer REFERENCES items
) WITHOUT OIDS;

CREATE TABLE items (
  id serial PRIMARY KEY,
  group integer NOT NULL REFERENCES groups,
  ts timestamp DEFAULT now()
) WITHOUT OIDS;

The index:
CREATE INDEX groups_last_ts
  ON groups
  USING btree
  (items_ts(last_post));

Plpgsql function items_ts returns timestamp for given item, which will never 
change(that's my assumption), so in fact according to definition IT IS 
immutable fuction.

Unfortunately, whenever I update last_item column in groups, I get wrong 
results, so I query like this:

SELECT * FROM groups WHERE items_ts(last_item)  now() - interval '1 week'

returns outdated results

I do realize about other ways for solving this problem, however I would prefer 
if it worked in the way described above.

/Grzegorz


From before you wrote:

When I insert new record to children table, select over parents with function 
gives wrong(outdated) results.

Which is not the same as what you are describing above.


Furthermore:

I created functional index on parents with function, which selects max value 
of timestamp from child elements(for given parent_id).

Is not the same as:

..items_ts returns the timestamp for the given item...


If all items_ts did was return the timestamp of the provided child then when 
you update the last_item column on groups (however you would decide to do 
that) a new index entry would be created that stores the timestamp for the 
specified child id.  As long as the child's timestamp doesn't change (or become 
deleted) then the index will maintain the correct value.

Given that you are seeing outdated results that means you are changing the 
items table without updating the groups table in a corresponding manner but 
instead are expecting the index function to somehow magically update.  That is 
not how the system works.

If you want to put forth a self-contained example with descriptions of exactly 
where you believe there is a problem then maybe we can help you understand 
better.  As it stands now you have provided two different descriptions of your 
situation.  The first one seems to be the most accurate and based upon that 
description the advice you have been given is correct.  The second example is 
incomplete but could indeed work (given specific assumptions).  The idea you 
are suggesting is that you maintain the id of the most recent item on the 
group table then use a functional index to cache the timestamp of that child. 
 The question becomes how do you update the item id on the groups table 
when you add new records to items.

David J.







-- 
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] Tigger after delete with plpgsql

On Friday, August 31, 2012 03:14:15 PM Fellipe Henrique wrote:
 CREATE TRIGGER nfentrada_item_tr1
   BEFORE DELETE
   ON public.nfentrada_item FOR EACH ROW
   EXECUTE PROCEDURE public.nfentrada_item_ad0();
 
 here is my nfentrada_item_ad0();
 
 delete
   from MOVIMENTO
   where (IDEMPRESA = OLD.idempresa) and
 (upper(TABELA) = 'NFENTRADA_ITEM') and
 (CODIGO = OLD.idnfentrada_item);
   return old;
 
 I just want to delete all row in my MOVIMENTO table with these
 conditionals..
 

And the error appears when you do a DELETE FROM public.nfentrada_item? Can you 
post the entire function declaration with the CREATE comand?



-- 
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] Refreshing functional index

2012-08-31 Thread Grzegorz Tańczyk


Hello,

W dniu 2012-08-31 20:25, David Johnston pisze:
The question becomes how do you update the item id on the groups 
table when you add new records to items. 


I have a trigger on items table:

CREATE TRIGGER items_insert
  BEFORE INSERT
  ON items
  FOR EACH ROW
  EXECUTE PROCEDURE items_oninsert();

CREATE OR REPLACE FUNCTION items_oninsert()
  RETURNS trigger AS
$BODY$
BEGIN
  UPDATE groups SET last_item = NEW.id WHERE id=NEW.group AND 
(last_item IS NULL OR last_itemNEW.id);

   RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Thanks!

--
Regards,
  Grzegorz



--
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] Tigger after delete with plpgsql

2012-08-31 Thread fellipeh

Yes, the error appears when I delete nfentrada_item row

Here is code:





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Tigger-after-delete-with-plpgsql-tp5722154p5722173.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Tigger after delete with plpgsql

On Friday, August 31, 2012 12:12:32 PM fellipeh wrote:
 Yes, the error appears when I delete nfentrada_item row
 View this message in context:
 http://postgresql.1045698.n5.nabble.com/Tigger-after-delete-with-plpgsql-tp
 5722154p5722173.html Sent from the PostgreSQL - general mailing list archive
 at Nabble.com.

Well ... I don't know. That runs fine here. Can you post the actual error 
message you get? Or a \d on both of those tables.



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

[GENERAL] Too far out of the mainstream

2012-08-31 Thread Andy Yoder

Hello all,

I would like the community's input on a topic.  The words too far out of the 
mainstream are from an e-mail we received from one of our clients, describing 
the concern our client's IT group has about our use of PostgreSQL in our shop.  
The group in question supports multiple different databases, including Oracle, 
MySQL, SQLServer, DB2, and even some non-relational databases (think Cobol and 
file-based storage), each type with a variety of applications and support 
needs.  We are in the running for getting a large contract from them and need 
to address their question:  What makes PostgreSQL no more risky than any other 
database?

Thanks in advance for your input.

Andy Yoder 




-- 
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] Tigger after delete with plpgsql

2012-08-31 Thread fellipeh

Here is error msg:
http://postgresql.1045698.n5.nabble.com/file/n5722180/erro_PG.png 

sorry, but in portuguese..



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Tigger-after-delete-with-plpgsql-tp5722154p5722180.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Too far out of the mainstream

So do they ever go to a site that ends in .org or .info?  Tell them to
stop it right now, as they are relying on PostgreSQL for those sites
to resolve, and PostgreSQL is too far out of the mainstream.  Once
they've stopped using or visiting .org and .info sites tell them to
get back to you.

On Fri, Aug 31, 2012 at 1:25 PM, Andy Yoder ayo...@airfacts.com wrote:
 Hello all,

 I would like the community's input on a topic.  The words too far out of the 
 mainstream are from an e-mail we received from one of our clients, 
 describing the concern our client's IT group has about our use of PostgreSQL 
 in our shop.  The group in question supports multiple different databases, 
 including Oracle, MySQL, SQLServer, DB2, and even some non-relational 
 databases (think Cobol and file-based storage), each type with a variety of 
 applications and support needs.  We are in the running for getting a large 
 contract from them and need to address their question:  What makes 
 PostgreSQL no more risky than any other database?

 Thanks in advance for your input.

 Andy Yoder




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



-- 
To understand recursion, one must first understand recursion.


-- 
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] Too far out of the mainstream

 -Original Message-
 From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
 ow...@postgresql.org] On Behalf Of Andy Yoder
 Sent: Friday, August 31, 2012 3:25 PM
 To: pgsql-general@postgresql.org
 Cc: Andy Yoder
 Subject: [GENERAL] Too far out of the mainstream

 Hello all,

 I would like the community's input on a topic.  The words too far out of
the
 mainstream are from an e-mail we received from one of our clients,
 describing the concern our client's IT group has about our use of
PostgreSQL
 in our shop.  The group in question supports multiple different databases,
 including Oracle, MySQL, SQLServer, DB2, and even some non-relational
 databases (think Cobol and file-based storage), each type with a variety
of
 applications and support needs.  We are in the running for getting a large
 contract from them and need to address their question:  What makes
 PostgreSQL no more risky than any other database?

 Thanks in advance for your input.

 Andy Yoder

Postgres, like the other database products out there, attempts to adhere to
an independent standard (SQL) as well as provide additional functionality
deemed useful but that falls outside the standard.  Its long existence and
usage in many different businesses and situations, as well as it regular
major-release schedule, shows that it is indeed mainstream.  Even in a
worse-case scenario, were all new development to stop, prior stable releases
are available and proven in the market and already released under and
open-source license that cannot be revoked - unlike other licenses in the
market.

Aside from all that I would politely ask the client's IT group for specific
and detailed concerns that can be addressed with facts and not via simple
assertions that it works for other people.

If the client's IT group is going to be supporting the database then
mainstream has a different meaning than if all database management is
going to done by you and they are worried that PostgreSQL is insecure (which
is not just a function of the database but your entire infrastructure) or is
going to be too slow for the amount of data they are going to be accessing.
Specifics...

David J.

-- 
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] Too far out of the mainstream

On Fri, Aug 31, 2012 at 02:25:13PM -0500, Andy Yoder wrote:

 I would like the community's input on a topic.  The words too far
 out of the mainstream are from an e-mail we received from one of
 our clients, describing the concern our client's IT group has about
 our use of PostgreSQL in our shop.  The group in question supports
 multiple different databases, including Oracle, MySQL, SQLServer,
 DB2, and even some non-relational databases (think Cobol and
 file-based storage), each type with a variety of applications and
 support needs.  We are in the running for getting a large contract
 from them and need to address their question: What makes PostgreSQL
 no more risky than any other database?

This canard has been going around for years.  Anyone who thinks that
MySQL, with its sketchy guarantees of data integrity and persistence,
is mainstream-acceptable but Postgres isn't because they haven't read
about it in InfoWorld (or wherever they get their news) is just
believing too much of whatever marketing material their vendors are
shoveling at them.

A response to this sort of question from the .org TLD redelegation is
still available online:
http://archive.icann.org/en/tlds/org/questions-to-applicants-13.htm#Response13TheInternetSocietyISOC.
The details in that answer are all obsolete, of course, since it's
from several years (and Postgres versions) ago, but you can use it as
a cheat sheet in formulating your answer.  For what it's worth, .org
was redelegated from Verisign to Public Interest Registry, and the
resulting system used PostgreSQL (instead of Oracle).  

There are more recent community marketing materials around, but I
thought I'd point you to this one because the kind of pressure we were
under at the time was pretty much exactly as you're describing.

Good luck.

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] Tigger after delete with plpgsql

On Friday, August 31, 2012 12:41:42 PM fellipeh wrote:
 Here is error msg:
 http://postgresql.1045698.n5.nabble.com/file/n5722180/erro_PG.png
 
 sorry, but in portuguese..
 
 
 
 --
 View this message in context:
 http://postgresql.1045698.n5.nabble.com/Tigger-after-delete-with-plpgsql-tp
 5722154p5722180.html Sent from the PostgreSQL - general mailing list archive
 at Nabble.com.

What is the function movimento_ad0()?




-- 
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] Tigger after delete with plpgsql

2012-08-31 Thread fellipeh

Found the error... my movimento_ad0() was set to statement .. I change to
Row, and works fine now...

Thanks for all





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Tigger-after-delete-with-plpgsql-tp5722154p5722190.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Too far out of the mainstream

On Fri, Aug 31, 2012 at 2:05 PM, Andrew Sullivan a...@crankycanuck.ca wrote:
On Fri, Aug 31, 2012 at 02:25:13PM -0500, Andy Yoder wrote:

I would like the community's input on a topic. The words too far
out of the mainstream are from an e-mail we received from one of
our clients, describing the concern our client's IT group has about
our use of PostgreSQL in our shop. The group in question supports
multiple different databases, including Oracle, MySQL, SQLServer,
DB2, and even some non-relational databases (think Cobol and
file-based storage), each type with a variety of applications and
support needs. We are in the running for getting a large contract
from them and need to address their question: What makes PostgreSQL
no more risky than any other database?

This canard has been going around for years. Anyone who thinks that
MySQL, with its sketchy guarantees of data integrity and persistence,
is mainstream-acceptable but Postgres isn't because they haven't read
about it in InfoWorld (or wherever they get their news) is just
believing too much of whatever marketing material their vendors are
shoveling at them.

A response to this sort of question from the .org TLD redelegation is
still available online:
http://archive.icann.org/en/tlds/org/questions-to-applicants-13.htm#Response13TheInternetSocietyISOC.
The details in that answer are all obsolete, of course, since it's
from several years (and Postgres versions) ago, but you can use it as
a cheat sheet in formulating your answer. For what it's worth, .org
was redelegated from Verisign to Public Interest Registry, and the
resulting system used PostgreSQL (instead of Oracle).

One of the most fascinating things to come out of the whole Afilias
winning the right to host the .org and .info domains was Oracle's PR
response to the suggestion of using postgresql. Wish I could find it.
Andrew might have it archived somewhere. But the Oracle PR flak
basically outright lied about PostgreSQL, saying it didn't support
transactions. This bald faced lie might be understandable if
transactions were bolted onto PostgreSQL at some late date after its
inception, but transactions were pretty much built in from the
beginning. I.e. Oracle will say what they have to to win, and if that
means looking you in the face and lying about the competition, they
won't hesitate to do it.

--
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] Too far out of the mainstream

2012-08-31 Thread Steve Atkins


On Aug 31, 2012, at 12:45 PM, Scott Marlowe scott.marl...@gmail.com wrote:

 So do they ever go to a site that ends in .org or .info?  Tell them to
 stop it right now, as they are relying on PostgreSQL for those sites
 to resolve, and PostgreSQL is too far out of the mainstream.  Once
 they've stopped using or visiting .org and .info sites tell them to
 get back to you.

Mmm. Don't push this line of argument too hard. As I understand it,
Postgresql is used by the registry to keep track of their customers -
whois data, effectively.

The actual resolution is handled by a different database, or was back
when I knew the details of that end of .org.

I'm sure there's an Access database somewhere in Facebook, but that
doesn't mean Facebook runs on Access. :)

Cheers,
  Steve



-- 
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] Too far out of the mainstream

2012-08-31 Thread Geert Mak

There are more recent community marketing materials around, but I
thought I'd point you to this one because the kind of pressure we were
under at the time was pretty much exactly as you're describing.

There is this case studies section as well -

http://www.postgresql.org/about/casestudies/

Which appear to me a little old and a little too little, one could try to add
more, perhaps.

Also the limitations page is interesting -

http://www.postgresql.org/about/

Also you have what people say about it -

http://www.postgresql.org/about/quotesarchive/

And awards -

http://www.postgresql.org/about/awards/

We have been using PostgreSQL for about 10 years and are currently developing
quite big data crunching application which should handle between 25 and 100
million objects which go over object-relational mapping and may easily have
20-30 properties each, so we might go into 2-3 billion rows. We have a master
database which is replicated via asynchronous streaming replication into
read-only slaves, where the data crunching takes place. The whole setup runs on
cloud servers, so it is easy to add more slaves when more capacity is needed.

I should say, indeed, the fame of PostgreSQL is quite smaller than its
qualities. But I guess that's the fate of most professional things which simply
work, like vim.

Our approach is that we are a solutions provider, and we use each successful
project as a reference and we sign with our heads, that it will work. But I
guess your situation is slightly different.

--
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] Too far out of the mainstream

On Fri, Aug 31, 2012 at 04:00:06PM -0600, Scott Marlowe wrote:

 One of the most fascinating things to come out of the whole Afilias
 winning the right to host the .org and .info domains was Oracle's PR
 response to the suggestion of using postgresql.  Wish I could find it.

It was only the .org case.

The .org redelegation, more than the start up of .info, was quite
controversial.  Nobody knew how much a new TLD was likely to make, but
at redelegation .org contained about 5 million domains.  At $6.00 per
name per year wholesale (of which Afilias, as a vendor to PIR, took
only a part, I wish to emphasise), there was a non-trivial amount of
money involved in the operation of .org, so the bidding was pretty
heavy.  Also, at the time it wasn't clear to anyone whether ICANN
would ever permit more labels in the root zone (now, of course, we
know that the plan is thousands of new domains.  It's feast or famine
in the domain name industry ;-).

The Oracle stuff is all part of the archived public comments on the
ICANN site.  You can find the whole sorry controversy here:
http://forum.icann.org/org-eval/gartner-report/.  Oracle's
mouthpiece, Jenny Gelhausen, did seem to have conflated PostgreSQL and
MySQL in the remarks.  I found particularly amusing the claim in those
remarks that Postgres was used primarily in the embedded market,
because of course Postgres has very frequently been attacked for its
resistance to proposed features that render it more suitable for the
embedded market.  

The Gartner report itself was controversial: ISC, who also promised to
use PostgreSQL for its back end, got a lower grade on the back end
than did Afilias.

Anyway, this is all an amusing walk down memory lane.  Thanks for the
reminder!

Best,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] Too far out of the mainstream

On Fri, Aug 31, 2012 at 4:14 PM, Steve Atkins st...@blighty.com wrote:

 On Aug 31, 2012, at 12:45 PM, Scott Marlowe scott.marl...@gmail.com wrote:

 So do they ever go to a site that ends in .org or .info?  Tell them to
 stop it right now, as they are relying on PostgreSQL for those sites
 to resolve, and PostgreSQL is too far out of the mainstream.  Once
 they've stopped using or visiting .org and .info sites tell them to
 get back to you.

 Mmm. Don't push this line of argument too hard. As I understand it,
 Postgresql is used by the registry to keep track of their customers -
 whois data, effectively.

 The actual resolution is handled by a different database, or was back
 when I knew the details of that end of .org.

 I'm sure there's an Access database somewhere in Facebook, but that
 doesn't mean Facebook runs on Access. :)

Unless things have changed, Andrew Sullivan in this message
http://archives.postgresql.org/pgsql-advocacy/2002-09/msg00012.php
says:

All interactions with the shared registry system, and any whois
queries against whois.afilias.net, are served by a PostgreSQL
database.

So yeah of course direct service of dns lookup is done via bind
servers operating off harvested data, but whois comes right out of a
pg database, and live updates go right into a pg database.


-- 
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] Too far out of the mainstream

On Fri, Aug 31, 2012 at 4:47 PM, Andrew Sullivan a...@crankycanuck.ca wrote:
 Anyway, this is all an amusing walk down memory lane.  Thanks for the
 reminder!

Hard to believe it was so long ago!


-- 
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] Too far out of the mainstream

2012-08-31 Thread Steve Atkins


On Aug 31, 2012, at 4:15 PM, Scott Marlowe scott.marl...@gmail.com wrote:
 
 Unless things have changed, Andrew Sullivan in this message
 http://archives.postgresql.org/pgsql-advocacy/2002-09/msg00012.php
 says:
 
 All interactions with the shared registry system, and any whois
 queries against whois.afilias.net, are served by a PostgreSQL
 database.

That's likely still the case, a decade later.

 So yeah of course direct service of dns lookup is done via bind
 servers operating off harvested data,

dot-org is actually powered by UltraDNS tech (since bought out by
Afilias) rather than bind. And that is directly SQL database backed,
though likely not the database we know and love.

So unless someone from Afilias pops up and tells us they're using
PG there too I'm a little cautious about mentioning PostgreSQL, .org
and DNS together.

 but whois comes right out of a
 pg database, and live updates go right into a pg database.

Yup.

Cheers,
  Steve


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

[GENERAL] CASE/WHEN behavior with NULLS

2012-08-31 Thread Thalis Kalfigkopoulos

Hello all,

I have a query that presents a sum() where in some records it's NULL
because all members of the group are NULL.
I decided I wanted to see a pretty 0 instead of NULL since it fits the
logic of the app.

This didn't work as expected (the NULL's persisted):
...CASE sum(foo) WHEN NULL THEN 0 ELSE sum(foo) END...

Whereas changing it to:
...CASE WHEN sum(foo) IS NULL THEN 0 ELSE sum(foo) END...
it works as expected, substituting the sum()'s that are NULL to zeros.

Is that expected behavior? Do i misunderstand how CASE/WHEN works?

Running:  PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC)
3.4.6, 32-bit

TIA,
Thalis K.


-- 
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] Too far out of the mainstream

On Fri, Aug 31, 2012 at 03:14:30PM -0700, Steve Atkins wrote:
 
 Mmm. Don't push this line of argument too hard. As I understand it,
 Postgresql is used by the registry to keep track of their customers -
 whois data, effectively.

No, the Postgres back end in the Afilias implementation I worked on
(it is as far as I know still there, but I don't work for Afilias any
more and I don't have any special knowledge about their actual
implementation as in production today) is for the domain name
registry.  That means that all the registration data -- which includes
the data necessary to produce DNS responses -- is in that database.
In addition, I worked on and deployed a system that generated directly
all the DNS zone data directly from the PostgreSQL databases.

It _is_ true, of course, that every DNS lookup is not a direct query
of that database system.  But unless Afilias has changed their
implementation very dramatically (and I've no reason to believe they
have), you could not get to any web site ending in .org (or, for that
matter, .info, .in, .aero, .mobi, and a number of others) without the
services of PostgreSQL.

Best,

A

-- 
Andrew Sullivan
a...@anvilwalrusden.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] CASE/WHEN behavior with NULLS

On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos tkalf...@gmail.com wrote:

 Hello all,
 
 I have a query that presents a sum() where in some records it's NULL
 because all members of the group are NULL.
 I decided I wanted to see a pretty 0 instead of NULL since it fits the
 logic of the app.
 
 This didn't work as expected (the NULL's persisted):
 ...CASE sum(foo) WHEN NULL THEN 0 ELSE sum(foo) END...

Guessing this form effectively evaluates to 

WHEN sum(foo) = NULL instead of IS NULL and thus the wrong answer:


 
 Whereas changing it to:
 ...CASE WHEN sum(foo) IS NULL THEN 0 ELSE sum(foo) END...
 it works as expected, substituting the sum()'s that are NULL to zeros.
 
 Is that expected behavior? Do i misunderstand how CASE/WHEN works?
 

Yes.

That said you might want to try

SUM(COALESCE(foo, 0))

or

SUM(case when foo is null then 0 else foo end)

Your current attempt does not handle mixed NULL and NOT NULL the way most 
people would want it to (though maybe you do...)

 Running:  PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC)
 3.4.6, 32-bit
 
 TIA,
 Thalis K.
 
 

David J


-- 
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] CASE/WHEN behavior with NULLS

2012-08-31 Thread Tom Lane

David Johnston pol...@yahoo.com writes:
 On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos tkalf...@gmail.com wrote:
 This didn't work as expected (the NULL's persisted):
 ...CASE sum(foo) WHEN NULL THEN 0 ELSE sum(foo) END...

 Guessing this form effectively evaluates to 
 WHEN sum(foo) = NULL instead of IS NULL and thus the wrong answer:

Yeah, I think that's right.

 That said you might want to try
 SUM(COALESCE(foo, 0))

Actually I'd go with COALESCE(SUM(foo), 0) since that requires only
one COALESCE operation, not one per row.

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] CASE/WHEN behavior with NULLS

On Aug 31, 2012, at 21:52, Tom Lane t...@sss.pgh.pa.us wrote:

 David Johnston pol...@yahoo.com writes:
 On Aug 31, 2012, at 19:14, Thalis Kalfigkopoulos tkalf...@gmail.com wrote:
 This didn't work as expected (the NULL's persisted):
 ...CASE sum(foo) WHEN NULL THEN 0 ELSE sum(foo) END...
 
 Guessing this form effectively evaluates to 
 WHEN sum(foo) = NULL instead of IS NULL and thus the wrong answer:
 
 Yeah, I think that's right.
 
 That said you might want to try
 SUM(COALESCE(foo, 0))
 
 Actually I'd go with COALESCE(SUM(foo), 0) since that requires only
 one COALESCE operation, not one per row.
 
 

These are not equivalent if some values of foo are not-null and you want the 
sum of all non-null values while replacing any nulls with zero.  So the 
decision depends on what and why you are summing. 

As an alternative for the original question the coalesce(sum(foo),0) form is 
indeed better.

David J.



-- 
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] CASE/WHEN behavior with NULLS

2012-08-31 Thread Chris Angelico

On Sat, Sep 1, 2012 at 12:07 PM, David Johnston pol...@yahoo.com wrote:
 These are not equivalent if some values of foo are not-null and you want the 
 sum of all non-null values while replacing any nulls with zero.  So the 
 decision depends on what and why you are summing.

It comes to the same result with SUM though isn't it?

ChrisA


-- 
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] CASE/WHEN behavior with NULLS

2012-08-31 Thread Tom Lane

David Johnston pol...@yahoo.com writes:
 On Aug 31, 2012, at 21:52, Tom Lane t...@sss.pgh.pa.us wrote:
 David Johnston pol...@yahoo.com writes:
 That said you might want to try
 SUM(COALESCE(foo, 0))

 Actually I'd go with COALESCE(SUM(foo), 0) since that requires only
 one COALESCE operation, not one per row.

 These are not equivalent if some values of foo are not-null and you want the 
 sum of all non-null values while replacing any nulls with zero.  So the 
 decision depends on what and why you are summing. 

But SUM() ignores input nulls, so I think they really are equivalent.
I agree that in a lot of other cases (for instance MAX), you'd have to
think harder about which behavior you wanted.

The key point here is that whatever is inside the aggregate function
call is computed once per row, and then the aggregate is applied to
those results, and then whatever is outside the aggregate is done once
on the aggregate's result.  SQL's syntax doesn't make this too obvious,
but you really have to grasp that to make any sense of what's happening.

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] CASE/WHEN behavior with NULLS

On Aug 31, 2012, at 22:49, Tom Lane t...@sss.pgh.pa.us wrote:

 David Johnston pol...@yahoo.com writes:
 On Aug 31, 2012, at 21:52, Tom Lane t...@sss.pgh.pa.us wrote:
 David Johnston pol...@yahoo.com writes:
 That said you might want to try
 SUM(COALESCE(foo, 0))
 
 Actually I'd go with COALESCE(SUM(foo), 0) since that requires only
 one COALESCE operation, not one per row.
 
 These are not equivalent if some values of foo are not-null and you want the 
 sum of all non-null values while replacing any nulls with zero.  So the 
 decision depends on what and why you are summing. 
 
 But SUM() ignores input nulls, so I think they really are equivalent.
 I agree that in a lot of other cases (for instance MAX), you'd have to
 think harder about which behavior you wanted.
 

This I did not know/recall, was assuming nulls poisoned the result.

David J.

-- 
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] Too far out of the mainstream