Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 1EB266325EB for ; Thu, 8 Oct 2009 05:25:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 63322-07 for ; Thu, 8 Oct 2009 08:24:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from relay.pcl-ipout02.plus.net (relay.pcl-ipout02.plus.net [212.159.7.100]) by mail.postgresql.org (Postfix) with ESMTP id 403F763225E for ; Thu, 8 Oct 2009 05:24:49 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ApoEAPc+zUrUnw4T/2dsb2JhbADYYoQqBA Received: from pih-relay06.plus.net ([212.159.14.19]) by relay.pcl-ipout02.plus.net with ESMTP; 08 Oct 2009 09:24:46 +0100 Received: from [84.51.143.99] (helo=server3.office.archonet.com) by pih-relay06.plus.net with esmtp (Exim) id 1MvoIo-0004TS-Hn; Thu, 08 Oct 2009 09:24:46 +0100 Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTPA id 4F1DE27407A; Thu, 8 Oct 2009 09:24:46 +0100 (BST) Message-ID: <4ACDA1CE.8060207@archonet.com> Date: Thu, 08 Oct 2009 09:24:46 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Gary Chambers CC: pgsql-sql@postgresql.org Subject: Re: Function Anomaly? References: <302670f20910071925g30322072oc9395e83d592ad2b@mail.gmail.com> In-Reply-To: <302670f20910071925g30322072oc9395e83d592ad2b@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Plusnet-Relay: 345c3bea0175a2c17e9d2aa4999947ad X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.349 tagged_above=-10 required=5 tests=AWL=-0.609, BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 200910/27 X-Sequence-Number: 33226 Gary Chambers wrote: > CREATE OR REPLACE FUNCTION getnote(INTEGER, BIGINT) RETURNS getnote_t AS > When I call it with a row where n.is_private is TRUE and n.ownerid IS > TRUE, I receive a single row of all null values: > > notesdb=# select * from getnote(1, 2); > When I submit the query directly (getnote.sql is simply the query with > the CASE statement forced to false): > > notesdb=# \i getnote.sql > nid | ownerid | ownername | hostname | entrytime | is_private | > is_active | notetext > -----+---------+-----------+----------+-----------+------------+-----------+---------- > (0 rows) You've defined getnote() as returning a single getnote_t value I think you probably want "RETURNS SETOF getnote_t". -- Richard Huxton Archonet Ltd