From ignacio.colmenero@micotan.com Fri Feb 11 18:51:42 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3DF228B9E2D for ; Fri, 11 Feb 2005 18:51:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17806-08 for ; Fri, 11 Feb 2005 18:51:19 +0000 (GMT) Received: from priv-edtnes57.telusplanet.net (outbound01.telus.net [199.185.220.220]) by svr1.postgresql.org (Postfix) with ESMTP id 740998B9E2B for ; Fri, 11 Feb 2005 18:51:18 +0000 (GMT) Received: from ignacio ([205.206.124.119]) by priv-edtnes57.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050211185117.RZHU6731.priv-edtnes57.telusplanet.net@ignacio> for ; Fri, 11 Feb 2005 11:51:17 -0700 From: "Ignacio Colmenero" To: Subject: ERROR: control reached end of function without RETURN Date: Fri, 11 Feb 2005 11:51:14 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01C5102F.FCD34A20" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcUQaqkO0sHUYr8zTYOGsRISmcJw9w== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20050211185117.RZHU6731.priv-edtnes57.telusplanet.net@ignacio> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.051 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200502/663 X-Sequence-Number: 73467 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C5102F.FCD34A20 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi all. I created a function, using EMS: CREATE OR REPLACE FUNCTION "geo_schema"."search_geo" () RETURNS SETOF varchar AS $body$ declare x_longlat cursor for select long_lat from h2s ; declare var_longlat public.geometry ; declare x_id varchar ; begin open x_longlat ; <> loop fetch x_longlat into var_longlat ; if not found then close x_longlat ; exit ; end if ; select field_id from geo_table where within(var_longlat, geo_polygon) limit 1 into x_id ; if x_id <> '' then return next x_id ; end if ; end loop ; end ; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; When I debug it (using EMS tools), it works fine, and I get 73 rows, but when I run it like: select * from search_geo() ; I get the error: ERROR: control reached end of function without RETURN What can be wrong? TIA Ignacio. ----------------------------------------------- Ignacio Colmenero Software Development Micotan Software Company Ltd. ------=_NextPart_000_0000_01C5102F.FCD34A20 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all.

I created a function, using EMS:

CREATE = OR REPLACE FUNCTION "geo_schema"."search_geo" () RETURNS SETOF = varchar AS

$body$

declare = x_longlat cursor for select long_lat from h2s ;

declare var_longlat public.geometry ;

declare = x_id varchar ;

begin

     open x_longlat ;

     <<loop1>>

     loop

         fetch x_longlat into var_longlat ;

         if not found then

            close x_longlat ;

            exit ;

         end if ;

         select field_id from geo_table

         where within(var_longlat, geo_polygon) limit 1 into x_id = ;

         if x_id <> '' then

            return next x_id ;

         end if ;

     end loop ;

end = ;

$body$

LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY = INVOKER;

When I debug it (using EMS = tools), it works fine, and I get 73 rows, but when I run it = like:

select = * from search_geo() ;

I get the error: ERROR:  control reached end of function = without RETURN

 

What can be wrong?

 

TIA

Ignacio.

 

----------------------------= -------------------

Ignacio = Colmenero

Software = Development

Micotan Software Company = Ltd.

 

------=_NextPart_000_0000_01C5102F.FCD34A20-- From tgl@sss.pgh.pa.us Fri Feb 11 19:28:32 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C44CA8BA0D0 for ; Fri, 11 Feb 2005 19:28:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21592-02 for ; Fri, 11 Feb 2005 19:28:17 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0D8A38B9EEC for ; Fri, 11 Feb 2005 19:28:15 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1BJSDgt012915; Fri, 11 Feb 2005 14:28:14 -0500 (EST) To: "Ignacio Colmenero" Cc: pgsql-general@postgresql.org Subject: Re: ERROR: control reached end of function without RETURN In-reply-to: <20050211185117.RZHU6731.priv-edtnes57.telusplanet.net@ignacio> References: <20050211185117.RZHU6731.priv-edtnes57.telusplanet.net@ignacio> Comments: In-reply-to "Ignacio Colmenero" message dated "Fri, 11 Feb 2005 11:51:14 -0700" Date: Fri, 11 Feb 2005 14:28:13 -0500 Message-ID: <12914.1108150093@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/665 X-Sequence-Number: 73469 "Ignacio Colmenero" writes: > I get the error: ERROR: control reached end of function without RETURN > What can be wrong? You don't have a RETURN statement. regards, tom lane From ignacio.colmenero@micotan.com Fri Feb 11 19:49:33 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D9F9B8BA0D1 for ; Fri, 11 Feb 2005 19:49:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23247-01 for ; Fri, 11 Feb 2005 19:49:18 +0000 (GMT) Received: from priv-edtnes46.telusplanet.net (defout.telus.net [199.185.220.240]) by svr1.postgresql.org (Postfix) with ESMTP id D6AA08BA0D0 for ; Fri, 11 Feb 2005 19:49:18 +0000 (GMT) Received: from ignacio ([205.206.124.119]) by priv-edtnes46.telusplanet.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with ESMTP id <20050211194917.VJHS289.priv-edtnes46.telusplanet.net@ignacio>; Fri, 11 Feb 2005 12:49:17 -0700 From: "Ignacio Colmenero" To: "'Tom Lane'" Cc: Subject: Re: ERROR: control reached end of function without RETURN Date: Fri, 11 Feb 2005 12:49:13 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcUQcXiQVbDy248vQI6rws5mTmDBYwAAUgsg X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: <12914.1108150093@sss.pgh.pa.us> Message-Id: <20050211194917.VJHS289.priv-edtnes46.telusplanet.net@ignacio> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/667 X-Sequence-Number: 73471 Thanks Tom. I didn't know I needed a RETURN statement at the end. I assumed that the "return next" statement would be enough. It works fine now. ----------------------------------------------- Ignacio Colmenero Software Development Micotan Software Company Ltd. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane Sent: February 11, 2005 12:28 PM To: Ignacio Colmenero Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: control reached end of function without RETURN "Ignacio Colmenero" writes: > I get the error: ERROR: control reached end of function without RETURN > What can be wrong? You don't have a RETURN statement. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq From herwig.goemans@gmail.com Mon Sep 13 11:23:06 2021 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mPk3M-0006UW-U6 for pgsql-general@arkaria.postgresql.org; Mon, 13 Sep 2021 11:23:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mPk3L-0004Ee-SV for pgsql-general@arkaria.postgresql.org; Mon, 13 Sep 2021 11:23:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mPk3L-0004Du-JV for pgsql-general@lists.postgresql.org; Mon, 13 Sep 2021 11:23:11 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mPk3J-0002Z1-N3 for pgsql-general@lists.postgresql.org; Mon, 13 Sep 2021 11:23:10 +0000 Received: by mail-ed1-x530.google.com with SMTP id c21so5984300edj.0 for ; Mon, 13 Sep 2021 04:23:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:from:subject:message-id:date:user-agent:mime-version :content-transfer-encoding:content-language; bh=pX6RU/neOSaQSVARwGV7l4hk2NY9z8xdd8jox7TxoI0=; b=hHiIETObddJbWhv+sSHveRoV+6CBWHD97ts6Y3AK26x1+C56zxhfdMrZjx29HJVBmt hK4JbvCp9G7jRybADbp2PCYeVCyyvfP7Jb2lkxwtGsdB8bpTYyZCqEyk+A7lWOJq6bep spb/ldN74pNUyIwPHgaKqVpeXOq0mvcxADAYL5yRQKPpHVGsqcg4Fj3bVHyK6/sqN/Hq 193TcVYD0QZ3qIq1NAxeZMnPjBtHv1DOO2NkM0kjl8MEB/AKFmBjfcQtlh1RAFecmjcH w0oftzj1jNtJw/mhrGQTINFdXBqkLjTfmQpH59DSH1wJyJ5B33ZUeQuU4SjlEoJXsVn3 TXNw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-transfer-encoding:content-language; bh=pX6RU/neOSaQSVARwGV7l4hk2NY9z8xdd8jox7TxoI0=; b=zGrEtpxs8+OuZJykvHePRt+GIh3u0DPW7K52OPV312ffvMpOMqBPgZhFYIODOF9t+r CYTJd9BZ5nbwq56OQjRiVfBh66/rwKbGNyERnxMFS+jZQKfw/VmQQcHypOoQXqu7kEyg msnve276fYM4ILmzQLTyzHebckXZK84xywDw4mQqdO5oD+HK6wB5KnADCLNeTrgTZSzq ol3cfbnt/bsqEGTpLQC1rJVxKSZ4Dy3721jmXMWn5a8qyxPdx9p2MudUurDNAY0UrjQ0 zr/A/sWizLHGaZ8/ZBjXMnjcDra0e3W82oSx0rgn3mkL5Fr2Bkni0Jnd/DNXYsGK1Xy0 Y8Wg== X-Gm-Message-State: AOAM5328H4m8TpB3iTj1De46ze2GKRcUQP9pQwMO5KjYUJzP3YGQ7wRe bFD9TF9koEC2o13xORACwIQy9MtJT1k= X-Google-Smtp-Source: ABdhPJwttfN61/bZt2jW3p0fm0blUPTpWMOj5heDt+0xSI5iSzCyqDF56x09DzOfNlB8L+to7E7V+w== X-Received: by 2002:aa7:c744:: with SMTP id c4mr12729148eds.0.1631532187475; Mon, 13 Sep 2021 04:23:07 -0700 (PDT) Received: from [192.168.178.48] ([213.219.163.134]) by smtp.gmail.com with ESMTPSA id ba29sm2329608edb.5.2021.09.13.04.23.06 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 13 Sep 2021 04:23:06 -0700 (PDT) To: pgsql-general@lists.postgresql.org From: Herwig Goemans Subject: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block. Message-ID: <868ae948-e3ca-c7ec-95a6-83cfc08ef750@gmail.com> Date: Mon, 13 Sep 2021 13:23:06 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.13.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: nl-BE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Support, I get an error in a stored procedure - not a stored function mind you - control reached end of function without RETURN The error occurs when doing an exit