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