From jim@luckydigital.com Sun Feb  23 00:59:05 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C8FF8474E5C
	for <pgsql-sql@postgresql.org>; Sat, 22 Feb 2003 19:59:05 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 58987-09 for <pgsql-sql@postgresql.org>;
	Sat, 22 Feb 2003 19:58:55 -0500 (EST)
Received: from luckydigital.com (www.luckydigital.com [203.109.157.4])
	by postgresql.org (Postfix) with ESMTP id 784D7474E4F
	for <pgsql-sql@postgresql.org>; Sat, 22 Feb 2003 19:58:54 -0500 (EST)
Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1])
	by luckydigital.com (8.11.6/8.10.0) with SMTP id h1N0wuC10937
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 13:58:56 +1300
Message-ID: <002f01c2dad6$c03bb470$c900a8c0@jax>
From: "James Cooper" <jim@luckydigital.com>
To: "sql" <pgsql-sql@postgresql.org>
Subject: sql question after upgrade
Date: Sun, 23 Feb 2003 13:58:59 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002C_01C2DB43.B6CB8C40"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/503
X-Sequence-Number: 11901

This is a multi-part message in MIME format.

------=_NextPart_000_002C_01C2DB43.B6CB8C40
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi just upgraded to 7.3.2 and imported my db schema with no probs!

Just wondering... why VOLATILE is being appended to my functions - can anyo=
ne ansewer me what this means? - should i be using joins instead, does this=
 make a difference? If so an example of this would be much appreciated!!

CREATE FUNCTION public.get_people_total(int4) RETURNS int8 AS '
select count(person_id) as total from person where person_id IN
(
select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
=3D c.cluster_id and c.c_id =3D $1 group by cp.person_id=20
);
' LANGUAGE 'sql' VOLATILE;



cheers all

James

------=_NextPart_000_002C_01C2DB43.B6CB8C40
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1141" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D1>
<P><FONT face=3DArial size=3D2>Hi just upgraded to 7.3.2 and imported my db=
 schema=20
with no probs!</FONT></P>
<P><FONT face=3DArial size=3D2>Just wondering... why VOLATILE is being appe=
nded to=20
my functions - can anyone ansewer me what this means? - should i be using j=
oins=20
instead, does this make a difference? If so an example of this would be muc=
h=20
appreciated!!</FONT></P>
<P><FONT face=3DArial size=3D2>CREATE FUNCTION public.get_people_total(int4=
) RETURNS=20
int8 AS '<BR>select count(person_id) as total from person where person_id=
=20
IN<BR>(<BR>select cp.person_id from cluster_person cp, cluster c where=20
cp.cluster_id =3D c.cluster_id and c.c_id =3D $1 group by cp.person_id <BR>=
);<BR>'=20
LANGUAGE 'sql' VOLATILE;</FONT></P>
<P><FONT face=3DArial size=3D2></FONT>&nbsp;</P>
<P></FONT><FONT face=3DArial size=3D2>cheers all</FONT></P>
<P><FONT face=3DArial size=3D2>James</FONT><FONT=20
size=3D1></P></FONT></DIV></BODY></HTML>

------=_NextPart_000_002C_01C2DB43.B6CB8C40--




From neilc@samurai.com Sun Feb  23 01:42:13 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7129F475461
	for <pgsql-sql@postgresql.org>; Sat, 22 Feb 2003 20:42:13 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 61557-01 for <pgsql-sql@postgresql.org>;
	Sat, 22 Feb 2003 20:42:02 -0500 (EST)
Received: from bob.samurai.com (bob.samurai.com [205.207.28.75])
	by postgresql.org (Postfix) with ESMTP id BEB9C474E4F
	for <pgsql-sql@postgresql.org>; Sat, 22 Feb 2003 20:42:02 -0500 (EST)
Received: from DU150.N224.ResNet.QueensU.CA (DU150.N224.ResNet.QueensU.CA
	[130.15.224.150]) by bob.samurai.com (Postfix) with ESMTP
	id 324081D82; Sat, 22 Feb 2003 20:42:06 -0500 (EST)
Subject: Re: sql question after upgrade
From: Neil Conway <neilc@samurai.com>
To: James Cooper <jim@luckydigital.com>
Cc: sql <pgsql-sql@postgresql.org>
In-Reply-To: <002f01c2dad6$c03bb470$c900a8c0@jax>
References: <002f01c2dad6$c03bb470$c900a8c0@jax>
Content-Type: text/plain
Organization: 
Message-Id: <1045964524.367.32.camel@tokyo>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 
Date: 22 Feb 2003 20:42:04 -0500
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/504
X-Sequence-Number: 11902

On Sat, 2003-02-22 at 19:58, James Cooper wrote:
> Just wondering... why VOLATILE is being appended to my functions - can
> anyone ansewer me what this means?

http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-createfunction.html

explains the meaning of IMMUTABLE, STABLE, and VOLATILE.

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC






From peter_e@gmx.net Sun Feb  23 02:14:22 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 9226F47580B
	for <pgsql-sql@postgresql.org>; Sat, 22 Feb 2003 21:14:22 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 68770-01 for <pgsql-sql@postgresql.org>;
	Sat, 22 Feb 2003 21:14:11 -0500 (EST)
Received: from mail.gmx.net (mail.gmx.net [213.165.64.20])
	by postgresql.org (Postfix) with SMTP id 04D00475921
	for <pgsql-sql@postgresql.org>; Sat, 22 Feb 2003 21:14:07 -0500 (EST)
Received: (qmail 2014 invoked by uid 0); 23 Feb 2003 02:14:10 -0000
Received: from p3E9E70DC.dip0.t-ipconnect.de (62.158.112.220)
	by mail.gmx.net (mp018-rz3) with SMTP; 23 Feb 2003 02:14:10 -0000
Date: Sun, 23 Feb 2003 03:24:30 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
X-X-Sender: peter@peter.localdomain
To: James Cooper <jim@luckydigital.com>
Cc: sql <pgsql-sql@postgresql.org>
Subject: Re: sql question after upgrade
In-Reply-To: <002f01c2dad6$c03bb470$c900a8c0@jax>
Message-ID: <Pine.LNX.4.44.0302230251250.2308-100000@peter.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/506
X-Sequence-Number: 11904

James Cooper writes:

> Just wondering... why VOLATILE is being appended to my functions - can
> anyone ansewer me what this means?

Read the reference page for CREATE FUNCTION and then decide whether it's
appropriate for your functions.  Old functions get it appended during the
dump because that is how the function was effectively treated in the
version you are dumping from, but you may choose to take advantage of the
finer granularity that is now available.

-- 
Peter Eisentraut   peter_e@gmx.net