From johnnybo1@mail.tele.dk Wed Feb  12 21:48:15 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 43538475A80
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 16:48:15 -0500 (EST)
Received: from pfepb.post.tele.dk (pfepb.post.tele.dk [193.162.153.3])
	by postgresql.org (Postfix) with ESMTP id A87CC4759BD
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 16:48:14 -0500 (EST)
Received: from johnny (0x50c49adc.adsl-fixed.tele.dk [80.196.154.220])
	by pfepb.post.tele.dk (Postfix) with SMTP id 2C4065EE0A3
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 22:48:14 +0100 (CET)
Message-ID: <000801c2d2e0$edd32420$dc9ac450@johnny>
From: "Johnny Kristensen" <johnnybo1@mail.tele.dk>
To: <pgsql-sql@postgresql.org>
Subject: SQL Functions vs PL/PgSQL
Date: Wed, 12 Feb 2003 22:51:41 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0005_01C2D2E9.4F24F430"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/271
X-Sequence-Number: 11669

This is a multi-part message in MIME format.

------=_NextPart_000_0005_01C2D2E9.4F24F430
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi!
Does postgres store execution plan's for user-defined SQL functions as the =
PL/pgSQL interpreter does. And if so - will this only be prepared after an =
initial call to the function? Can't seem to find this information in the do=
cs!?
Thanks,
Johnny Kristensen

------=_NextPart_000_0005_01C2D2E9.4F24F430
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.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Does postgres store execution plan's for=
=20
user-defined SQL functions as the PL/pgSQL interpreter does. And if so - wi=
ll=20
this only be prepared after an initial call&nbsp;to the function?&nbsp;Can'=
t=20
seem to&nbsp;find&nbsp;this information&nbsp;in the docs!?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Johnny Kristensen</FONT></DIV></BODY></HTM=
L>

------=_NextPart_000_0005_01C2D2E9.4F24F430--




From jasiek@klaster.net Thu Feb  13 09:49:46 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 48A2C4759BD
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 04:49:46 -0500 (EST)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 937CF4759AF
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 04:49:45 -0500 (EST)
Received: from klaster.net (ph218.krakow.cvx.ppp.tpnet.pl [217.99.208.218])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id D20C92B884; Thu, 13 Feb 2003 10:41:39 +0100 (CET)
Message-ID: <3E4B639F.1090703@klaster.net>
Date: Thu, 13 Feb 2003 10:21:35 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Johnny Kristensen <johnnybo1@mail.tele.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Functions vs PL/PgSQL
References: <000801c2d2e0$edd32420$dc9ac450@johnny>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/280
X-Sequence-Number: 11678

Johnny Kristensen wrote:
> Hi!
> Does postgres store execution plan's for user-defined SQL functions as 
> the PL/pgSQL interpreter does. And if so - will this only be prepared 
> after an initial call to the function? Can't seem to find this 
> information in the docs!?
> Thanks,
> Johnny Kristensen
You need Postgresql 7.3 to do this. Check sql function "PREPARE".
Regards,
Tomasz Myrta





From josh@agliodbs.com Thu Feb  13 17:02:24 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C2DA6474E4F
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 12:02:24 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 8DE2E474E42
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 12:02:22 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2838123; Thu, 13 Feb 2003 09:02:34 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "Johnny Kristensen" <johnnybo1@mail.tele.dk>,
	<pgsql-sql@postgresql.org>
Subject: Re: SQL Functions vs PL/PgSQL
Date: Thu, 13 Feb 2003 09:02:05 -0800
User-Agent: KMail/1.4.3
References: <000801c2d2e0$edd32420$dc9ac450@johnny>
In-Reply-To: <000801c2d2e0$edd32420$dc9ac450@johnny>
MIME-Version: 1.0
Message-Id: <200302130902.05987.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/291
X-Sequence-Number: 11689

Johnny,

> Does postgres store execution plan's for user-defined SQL functions as the
> PL/pgSQL interpreter does. And if so - will this only be prepared after an
> initial call to the function? Can't seem to find this information in the
> docs!? Thanks,

Unless you know something I don't, I do not believe that PL/pgSQL stores=20
execution plans for functions.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco



From rmello@cc.usu.edu Thu Feb  13 18:43:14 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id A4C93474E5C
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 13:43:14 -0500 (EST)
Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.227.56])
	by postgresql.org (Postfix) with ESMTP id 31C15474E42
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 13:43:14 -0500 (EST)
Received: from roberto by brasileiro.net with local (Exim 3.35 #1 (Debian))
	id 18jOR3-0002JC-00; Thu, 13 Feb 2003 11:50:13 -0700
Date: Thu, 13 Feb 2003 11:50:13 -0700
From: Roberto Mello <rmello@cc.usu.edu>
To: Josh Berkus <josh@agliodbs.com>
Cc: Johnny Kristensen <johnnybo1@mail.tele.dk>,
	pgsql-sql@postgresql.org
Subject: Re: SQL Functions vs PL/PgSQL
Message-ID: <20030213185013.GA8794@cc.usu.edu>
References: <000801c2d2e0$edd32420$dc9ac450@johnny>
	<200302130902.05987.josh@agliodbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
In-Reply-To: <200302130902.05987.josh@agliodbs.com>
User-Agent: Mutt/1.4i
X-message-flag: Please consider using an e-mail program other than Outlook.
	It's insecure.
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/293
X-Sequence-Number: 11691

On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote:
> Johnny,
> 
> > Does postgres store execution plan's for user-defined SQL functions as the
> > PL/pgSQL interpreter does. And if so - will this only be prepared after an
> > initial call to the function? Can't seem to find this information in the
> > docs!? Thanks,
> 
> Unless you know something I don't, I do not believe that PL/pgSQL stores 
> execution plans for functions.

AFAIK execution plans for PL/pgSQL functions were cached once per backend.
No?

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
If CPU < 486 then upgrade (486,OS/2 3.0) : while < 586...



From tgl@sss.pgh.pa.us Fri Feb  14 00:06:35 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 15733474E4F
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 19:06:35 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 5E73E474E42
	for <pgsql-sql@postgresql.org>; Thu, 13 Feb 2003 19:06:34 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1E06U5u002168;
	Thu, 13 Feb 2003 19:06:30 -0500 (EST)
To: Roberto Mello <rmello@cc.usu.edu>
Cc: Josh Berkus <josh@agliodbs.com>,
	Johnny Kristensen <johnnybo1@mail.tele.dk>, pgsql-sql@postgresql.org
Subject: Re: SQL Functions vs PL/PgSQL 
In-reply-to: <20030213185013.GA8794@cc.usu.edu> 
References: <000801c2d2e0$edd32420$dc9ac450@johnny>
	<200302130902.05987.josh@agliodbs.com>
	<20030213185013.GA8794@cc.usu.edu>
Comments: In-reply-to Roberto Mello <rmello@cc.usu.edu>
	message dated "Thu, 13 Feb 2003 11:50:13 -0700"
Date: Thu, 13 Feb 2003 19:06:29 -0500
Message-ID: <2167.1045181189@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/305
X-Sequence-Number: 11703

Roberto Mello <rmello@cc.usu.edu> writes:
> On Thu, Feb 13, 2003 at 09:02:05AM -0800, Josh Berkus wrote:
>> Unless you know something I don't, I do not believe that PL/pgSQL stores 
>> execution plans for functions.

> AFAIK execution plans for PL/pgSQL functions were cached once per backend.
> No?

Yes.  This is explained in the docs ...
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html#PLPGSQL-OVERVIEW

			regards, tom lane



From johnnybo1@mail.tele.dk Fri Feb  14 09:11:44 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 1C39D474E42
	for <pgsql-sql@postgresql.org>; Fri, 14 Feb 2003 04:11:44 -0500 (EST)
Received: from pfepa.post.tele.dk (pfepa.post.tele.dk [193.162.153.2])
	by postgresql.org (Postfix) with ESMTP id 1860D475A37
	for <pgsql-sql@postgresql.org>; Fri, 14 Feb 2003 04:11:43 -0500 (EST)
Received: from johnny (0x50c49adc.adsl-fixed.tele.dk [80.196.154.220])
	by pfepa.post.tele.dk (Postfix) with SMTP
	id D8FB84802E6; Fri, 14 Feb 2003 10:09:07 +0100 (CET)
Message-ID: <005a01c2d409$39f6b960$dc9ac450@johnny>
From: "Johnny Kristensen" <johnnybo1@mail.tele.dk>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: <pgsql-sql@postgresql.org>
References: <000801c2d2e0$edd32420$dc9ac450@johnny>
	<200302130902.05987.josh@agliodbs.com>
Subject: Re: SQL Functions vs PL/PgSQL
Date: Fri, 14 Feb 2003 10:12:39 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/335
X-Sequence-Number: 11734

Of course what I meant was if postgres stores execution plans for queries i=
n user-defined SQL functions. From the docs I can see that PL/PgSQL uses th=
e SPI-interface to create and store execution plans for queries for each co=
nnection. But I'll use the PREPARE function like Thomasz Myrta suggested.

/Johnny

----- Original Message -----=20
From: "Josh Berkus" <josh@agliodbs.com>
To: "Johnny Kristensen" <johnnybo1@mail.tele.dk>; <pgsql-sql@postgresql.org>
Sent: Thursday, February 13, 2003 6:02 PM
Subject: Re: [SQL] SQL Functions vs PL/PgSQL


Johnny,

> Does postgres store execution plan's for user-defined SQL functions as the
> PL/pgSQL interpreter does. And if so - will this only be prepared after an
> initial call to the function? Can't seem to find this information in the
> docs!? Thanks,

Unless you know something I don't, I do not believe that PL/pgSQL stores=20
execution plans for functions.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco





---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)