From: Chris Lukenbill <chris@blendinteractive.com>
To: codeWarrior <gpatnude@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can't find which return type is incorrect.
Date: Fri, 11 Aug 2006 10:49:53 -0500
Message-ID: <fdb4b04c0608110849xdcf0d27q57c9b0218e56148e@mail.gmail.com> (raw)
In-Reply-To: <fdb4b04c0608110847x4dac0f23i39cab4b78ea4ecfc@mail.gmail.com>
References: <fdb4b04c0608110745t6dea55aeod40cf8e19a283dc1@mail.gmail.com>
<ebi7hs$ul9$1@news.hub.org>
<fdb4b04c0608110847x4dac0f23i39cab4b78ea4ecfc@mail.gmail.com>
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.
>
> ""Chris Lukenbill"" <chris@blendinteractive.com> 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
> >
> >
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chris@blendinteractive.com, gpatnude@hotmail.com
Subject: Re: Can't find which return type is incorrect.
In-Reply-To: <fdb4b04c0608110849xdcf0d27q57c9b0218e56148e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox