X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id D2C329FB1DD
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 11 Aug 2006 12:25:59 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 83381-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 11 Aug 2006 12:25:51 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id 6F3C09FB259
	for <pgsql-sql@postgresql.org>; Fri, 11 Aug 2006 12:25:50 -0300 (ADT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 76BE430AED; Fri, 11 Aug 2006 17:25:49 +0200 (MET DST)
From: "codeWarrior" <gpatnude@hotmail.com>
X-Newsgroups: pgsql.sql
Subject: Re: Can't find which return type is incorrect.
Date: Fri, 11 Aug 2006 08:28:19 -0700
Organization: iDynaTECH, Inc.
Lines: 141
Message-ID: <ebi7hs$ul9$1@news.hub.org>
References: <fdb4b04c0608110745t6dea55aeod40cf8e19a283dc1@mail.gmail.com>
Reply-To: "codeWarrior" <gpatnude@hotmail.com>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000B_01C6BD20.19A90BF0"
X-Complaints-To: usenet@news.hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
To: pgsql-sql@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.537 tagged_above=0 required=5
	tests=FORGED_HOTMAIL_RCVD2, HTML_30_40, HTML_MESSAGE
X-Spam-Level: *
X-Archive-Number: 200608/84
X-Sequence-Number: 26042

This is a multi-part message in MIME format.

------=_NextPart_000_000B_01C6BD20.19A90BF0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 ...)


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

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)" ???=20



  ""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.=20

  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?)=20
  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)=20
  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


------=_NextPart_000_000B_01C6BD20.19A90BF0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Can you show us the code for your SP ? =
I'd like to=20
see what the RETURNS statement is in the sp declaration (CREATE OR =
REPLACE=20
PROCEDURE sproc(type, type, type) RETURNS SETOF returntype AS =
...)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>You might reconsider your SELECT * FROM =
sproc() AS=20
() -- SELECT * retrieves ALL columns defined by the SP.... </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>What happens when you drop the "AS =
(columns)"=20
portion from your select ??? In other words -- what do you get when you =
simply=20
"SELECT * FROM sp_whatever(1, 0, 3)" ??? </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>""Chris Lukenbill"" &lt;<A=20
  =
href=3D"mailto:chris@blendinteractive.com">chris@blendinteractive.com</A>=
&gt;=20
  wrote in message <A=20
  =
href=3D"news:fdb4b04c0608110745t6dea55aeod40cf8e19a283dc1@mail.gmail.com"=
>news:fdb4b04c0608110745t6dea55aeod40cf8e19a283dc1@mail.gmail.com</A>...<=
/DIV>Alright.&nbsp;=20
  I have a very large amount of columns being returned by this stored =
procedure=20
  that I ported from MS SQL to Postgres.&nbsp; Now the problem I'm =
having is=20
  that when the select * from sp_whatever(1,0,3) as ( foo int, bar int, =
etc.) is=20
  executed the error "wrong record type supplied in RETURN NEXT =
CONTEXT".&nbsp;=20
  Now this immediately red flagged me to look at the types that I was =
returning=20
  and verify that those were the types that I was catching in the as=20
  statement.&nbsp; I went through to verify all of the variables and =
they are=20
  all correct as far as both name and the exact type.&nbsp; Therefore =
there are=20
  only a few things left that I'm thinking could be the problem. =
<BR><BR>1. Too=20
  many variables returned (there are 44 variables being returned).<BR>2. =
Some of=20
  the variables that are smallint in the select statement also do a if =
isnull=20
  type of logic that will return zero if they are null.&nbsp; (is that =
zero not=20
  coming back as a smallint then?) <BR>3. What I'm declaring as a =
variable type=20
  in postgresql isn't the variable type in PHP.&nbsp; The following are =
the=20
  different types of variables that I use:<BR>INT<BR>SMALLINT<BR>BIGINT =
(when I=20
  do a count(*))<BR>VARCHAR(xx)=20
  <BR>TEXT<BR>TIMESTAMP<BR>NUMERIC(19,2)<BR><BR><BR>Now the two there =
that I'm=20
  skeptical about are the timestamp and the numeric.<BR><BR>Thanks ahead =
of time=20
  for any ideas,<BR>Chris<BR><BR></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_000B_01C6BD20.19A90BF0--