Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 4584B1064878 for ; Fri, 11 Nov 2011 11:54:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 21544-04 for ; Fri, 11 Nov 2011 15:54:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail.csc.lt (mail.csc.lt [193.138.191.5]) by mail.postgresql.org (Postfix) with ESMTP id 4F59E1064877 for ; Fri, 11 Nov 2011 11:54:05 -0400 (AST) Received: (qmail 16511 invoked by uid 210); 11 Nov 2011 15:54:04 -0000 Received: from mail.nsoft.lt by mail (envelope-from , uid 201) with qmail-scanner-2.05st (clamdscan: 0.97/13916. spamassassin: 3.2.1. perlscan: 2.05st. Clear:RC:1(77.240.249.53):. Processed in 0.021367 secs); 11 Nov 2011 15:54:04 -0000 Received: from mail.nsoft.lt (77.240.249.53) by mail.csc.lt with SMTP; 11 Nov 2011 15:54:04 -0000 Received: from [192.168.16.47] (unknown [77.240.249.53]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (No client certificate requested) by mail.nsoft.lt (Postfix) with ESMTPS id 0D46D8E1BD for ; Fri, 11 Nov 2011 17:54:02 +0200 (EET) Message-ID: <4EBD4518.5070508@nsoft.lt> Date: Fri, 11 Nov 2011 17:54:00 +0200 From: Julius Tuskenis Organization: UAB nSoft User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: where clause + function, execution order References: <13DE38ECCB7CE74890E7150A7CCB610D3642DD47@exc1.wigeo.lan> In-Reply-To: <13DE38ECCB7CE74890E7150A7CCB610D3642DD47@exc1.wigeo.lan> Content-Type: multipart/alternative; boundary="------------010501030203010501080702" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201111/139 X-Sequence-Number: 45650 This is a multi-part message in MIME format. --------------010501030203010501080702 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hello, On 2011.11.11 17:38, Sorin Dudui wrote: > > Hi, > > I have the following function: > > =============================== > > CREATE OR REPLACE FUNCTION xxx(text) > > RETURNS SETOF vvvvv AS > > $BODY$ > > select a.x, a.y, > > CASE > > WHEN strpos($1,b.x) > 0 > > THEN b.x > > ELSE NULL > > END AS mp_hm > > from a LEFT JOIN b ON a.id=b.id > > $BODY$ > > LANGUAGE sql STABLE > > COST 1000 > > ROWS 10000; > > =============================== > > which I call as: > > select * from xxx(‘test0|test1‘) where a.x = ‘value’ > You should get an error as there is no "a" in this statement... > > I am wondering when the where clause (a.x = ‘value’) is executed. > After the select statement in the function finishes? Or is it appended > at the select statement in the function? > Function execute plan is prepared when creating it, so the "where" clause should check the function result not altering its execution.. -- Julius Tuskenis Head of the programming department UAB nSoft mob. +37068233050 --------------010501030203010501080702 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Hello,

On 2011.11.11 17:38, Sorin Dudui wrote:

Hi,

I have the following function:

===============================

CREATE OR REPLACE FUNCTION xxx(text)

RETURNS SETOF vvvvv AS

$BODY$

select a.x, a.y,

    CASE

    WHEN strpos($1,b.x) > 0

   THEN b.x

    ELSE NULL

    END AS mp_hm

from a LEFT JOIN b ON a.id=b.id

                $BODY$

LANGUAGE sql STABLE

COST 1000

ROWS 10000;

===============================

which I call as:

select * from xxx(‘test0|test1‘) where a.x = ‘value’

You should get an error as there is no "a" in this statement...

I am wondering when the where clause (a.x = ‘value’) is executed. After the select statement in the function finishes? Or is it appended at the select statement in the function?

Function execute plan is prepared when creating it, so the "where" clause should check the function result not altering its execution..

--
Julius Tuskenis
Head of the programming department
UAB nSoft
mob. +37068233050

--------------010501030203010501080702--