Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D3B729FA1C0 for ; Tue, 24 Apr 2007 12:55:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 47509-08 for ; Tue, 24 Apr 2007 12:55:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 7D9309F9F47 for ; Tue, 24 Apr 2007 12:55:46 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 5826376C28; Tue, 24 Apr 2007 16:52:53 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 119652740EE; Tue, 24 Apr 2007 16:52:53 +0100 (BST) Message-ID: <462E27D4.5020307@archonet.com> Date: Tue, 24 Apr 2007 16:52:52 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: "Wilkinson, Jim" , pgsql-sql@postgresql.org Subject: Re: Function to return a multiple colmn table or view References: <7CCC5BEF5E72394C963E529B54EB4A642A1A13@SD01ITMV12.PROD.NET> In-Reply-To: <7CCC5BEF5E72394C963E529B54EB4A642A1A13@SD01ITMV12.PROD.NET> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/233 X-Sequence-Number: 28233 Don't forget to cc: the list Wilkinson, Jim wrote: > Hi Richard, your example worked find , but when I substitue my view into > the function and the tale name, I get the following error: > > ERROR: wrong record type supplied in RETURN NEXT > CONTEXT: Pl/pqSQL function "create_view" line 11 at return next The columns you're returning from the function don't match it's definition. If the function is defined as RETURNS SETOF X then it needs to return columns that match X. If you need to you can create a custom type (see "CREATE TYPE" in the manuals). If you want one function that returns different types then you can't have it. -- Richard Huxton Archonet Ltd