X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 005719FB1EB for ; Fri, 11 Aug 2006 12:50:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 30396-01 for ; Fri, 11 Aug 2006 15:49:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id BB4889FB26A for ; Fri, 11 Aug 2006 12:49:54 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id n28so1079169nfc for ; Fri, 11 Aug 2006 08:49:53 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=OrWSDMa0m92SJKtetEjt17gxDppnFsJugI19/pisMilfpakpkbbzZDk5nFDXHgbf6NPrbNnYFQgVF/Qi/ZAY26m+l91pfzZt4nDOxLo+QMpenQ/5rxJGRgV6qHjnNJUHh6uhAW3CLs5yzDTSv/YhQmTGWGVhexptsgWipFVkTlo= Received: by 10.49.41.18 with SMTP id t18mr4042650nfj; Fri, 11 Aug 2006 08:49:53 -0700 (PDT) Received: by 10.48.243.15 with HTTP; Fri, 11 Aug 2006 08:49:53 -0700 (PDT) Message-ID: Date: Fri, 11 Aug 2006 10:49:53 -0500 From: "Chris Lukenbill" To: codeWarrior Subject: Re: Can't find which return type is incorrect. Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_48432_6663501.1155311393221" References: X-Google-Sender-Auth: 4dd0b042faf913cd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.134 tagged_above=0 required=5 tests=HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/85 X-Sequence-Number: 26043 ------=_Part_48432_6663501.1155311393221 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Let me try this again....My first one got delayed when my gmail defualted to the wrong account. (the response is inside codeWarrior's message. Thanks, Chris On 8/11/06, Chris Lukenbill wrote: > > > > On 8/11/06, codeWarrior wrote: > > > > Can you show us the code for your SP ? I'd like to see what the RETURNS > > statement is in the sp declaration (CREATE OR REPLACE PROCEDURE sproc(type, > > type, type) RETURNS SETOF returntype AS ...) > > > > Here is the SP > > CREATE OR REPLACE FUNCTION sp_content_tree (p_folder int, p_current int, > p_maxrows int) RETURNS SETOF RECORD as $$ > > DECLARE > returnRecord RECORD; > . > . > . > FOR returnRecord IN SELECT > pseudo_temp_out_simple.levelnum,view_content.*,pseudo_temp_out_simple.children > > FROM pseudo_temp_out_simple > INNER JOIN view_content ON itemID=item > ORDER BY > pseudo_temp_out_simple.displayorder,view_content.createdate > LOOP > RETURN NEXT returnRecord; > END LOOP; > > > > RETURN; > END; > $$ LANGUAGE plpgsql; > > > You might reconsider your SELECT * FROM sproc() AS () -- SELECT * > > retrieves ALL columns defined by the SP.... > > > > I'm looking into that currently.. > > > What happens when you drop the "AS (columns)" portion from your select ??? > > In other words -- what do you get when you simply "SELECT * FROM > > sp_whatever(1, 0, 3)" ??? > > > > I get the..."a column definition list is required for functions returning > "record" in"...error. > > ""Chris Lukenbill"" wrote in message news:fdb4b04c0608110745t6dea55aeod40cf8e19a283dc1@mail.gmail.com > > ... > > > > Alright. I have a very large amount of columns being returned by this > > stored procedure that I ported from MS SQL to Postgres. Now the problem I'm > > having is that when the select * from sp_whatever(1,0,3) as ( foo int, bar > > int, etc.) is executed the error "wrong record type supplied in RETURN NEXT > > CONTEXT". Now this immediately red flagged me to look at the types that I > > was returning and verify that those were the types that I was catching in > > the as statement. I went through to verify all of the variables and they > > are all correct as far as both name and the exact type. Therefore there are > > only a few things left that I'm thinking could be the problem. > > > > 1. Too many variables returned (there are 44 variables being returned). > > 2. Some of the variables that are smallint in the select statement also > > do a if isnull type of logic that will return zero if they are null. (is > > that zero not coming back as a smallint then?) > > 3. What I'm declaring as a variable type in postgresql isn't the > > variable type in PHP. The following are the different types of variables > > that I use: > > INT > > SMALLINT > > BIGINT (when I do a count(*)) > > VARCHAR(xx) > > TEXT > > TIMESTAMP > > NUMERIC(19,2) > > > > > > Now the two there that I'm skeptical about are the timestamp and the > > numeric. > > > > Thanks ahead of time for any ideas, > > Chris > > > > > ------=_Part_48432_6663501.1155311393221 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Let me try this again....My first one got delayed when my gmail defualted to the wrong account.  (the response is inside codeWarrior's message.

Thanks,

Chris

On 8/11/06, Chris Lukenbill <chris.lukenbill@gmail.com> wrote:


On 8/11/06, codeWarrior < gpatnude@hotmail.com> wrote:
Can you show us the code for your SP ? I'd like to see what the RETURNS statement is in the sp declaration (CREATE OR REPLACE PROCEDURE sproc(type, type, type) RETURNS SETOF returntype AS ...)

Here is the SP

CREATE OR REPLACE FUNCTION sp_content_tree (p_folder int, p_current int, p_maxrows int) RETURNS SETOF RECORD as $$

DECLARE
        returnRecord RECORD;
.
.
.
    FOR returnRecord IN    SELECT pseudo_temp_out_simple.levelnum,view_content.*,pseudo_temp_out_simple.children
                FROM pseudo_temp_out_simple
                INNER JOIN view_content ON itemID=item
                ORDER BY pseudo_temp_out_simple.displayorder,view_content.createdate
            LOOP
        RETURN NEXT returnRecord;
    END LOOP;

               

    RETURN;
END;
$$ LANGUAGE plpgsql;
 

You might reconsider your SELECT * FROM sproc() AS () -- SELECT * retrieves ALL columns defined by the SP....

I'm looking into that currently..
 

What happens when you drop the "AS (columns)" portion from your select ??? In other words -- what do you get when you simply "SELECT * FROM sp_whatever(1, 0, 3)" ???

I get the..."a column definition list is required for functions returning "record" in"...error.

Alright.  I have a very large amount of columns being returned by this stored procedure that I ported from MS SQL to Postgres.  Now the problem I'm having is that when the select * from sp_whatever(1,0,3) as ( foo int, bar int, etc.) is executed the error "wrong record type supplied in RETURN NEXT CONTEXT".  Now this immediately red flagged me to look at the types that I was returning and verify that those were the types that I was catching in the as statement.  I went through to verify all of the variables and they are all correct as far as both name and the exact type.  Therefore there are only a few things left that I'm thinking could be the problem.

1. Too many variables returned (there are 44 variables being returned).
2. Some of the variables that are smallint in the select statement also do a if isnull type of logic that will return zero if they are null.  (is that zero not coming back as a smallint then?)
3. What I'm declaring as a variable type in postgresql isn't the variable type in PHP.  The following are the different types of variables that I use:
INT
SMALLINT
BIGINT (when I do a count(*))
VARCHAR(xx)
TEXT
TIMESTAMP
NUMERIC(19,2)


Now the two there that I'm skeptical about are the timestamp and the numeric.

Thanks ahead of time for any ideas,
Chris



------=_Part_48432_6663501.1155311393221--