pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreturn setof records
3+ messages / 2 participants
[nested] [flat]
* return setof records
@ 2006-07-27 20:41 Chris Lukenbill <chris@blendinteractive.com>
0 siblings, 1 reply; 3+ messages in thread
From: Chris Lukenbill @ 2006-07-27 20:41 UTC (permalink / raw)
To: pgsql-sql
Alright, first I'll apologize for asking this question another time. I've
looked throught the archives and have found different ways on both the
archives and different ways in the the documentation to do this and
depending on which way I do it, I get different errors, so pick your
poison on that.
Everywhere I've looked the agreement was that making a call to the
function had to be done as follows:
SELECT * FROM sp_frontalerts_summary(1,'2006-07-27 18:08:09','2006-07-27
19:58:15' ) as (numitems int, region int);
So there isn't a question on that. My question is, how do I return a
"setof record" back to the application.
I've tried the following ways:
---------------------------------------------------------
CREATE OR REPLACE FUNCTION sp_frontalerts_summary (p_usernum int,
p_lastlogin TIMESTAMP,p_now TIMESTAMP) RETURNS SETOF RECORD AS $$
DECLARE
returnRecord RECORD;
BEGIN
FOR returnRecord in select count(item) as numitems,0 as region
from frontalerts
where usernum=p_usernum and pinup=0
LOOP
RETURN NEXT returnRecord;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------
Also have tried it with EXECUTE after the "FOR returnRecord in" and before
the "select count(item)..."
Also have tried to do just a "select count(item) as numitems,0 as region
from frontalerts where usernum=p_usernum and pinup=0" without any type of
returning (only a "RETURNS SETOF RECORD AS $$..") (the way that the
documentation talks about doing it in one area)
Now this is a little of a simplified version, but I have the same problem
with all of the stored procs.
If you know what I'm doing wrong, please let me know. Or if you have
anything I should possibly try, let me know that too. I've searched high
and low and really thought I had it this time, but I guess not.
Also, sorry for such an easy question, but I'm new to PostgreSQL so this
port from MS SQL to PostgreSQL on someone else's code is kickin my butt.
Thank you in adavance for any help.
Chris
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: return setof records
@ 2006-07-28 14:13 Michael Fuhr <mike@fuhr.org>
parent: Chris Lukenbill <chris@blendinteractive.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2006-07-28 14:13 UTC (permalink / raw)
To: Chris Lukenbill <chris@blendinteractive.com>; +Cc: pgsql-sql
On Thu, Jul 27, 2006 at 03:41:31PM -0500, Chris Lukenbill wrote:
> Everywhere I've looked the agreement was that making a call to the
> function had to be done as follows:
>
> SELECT * FROM sp_frontalerts_summary(1,'2006-07-27 18:08:09','2006-07-27
> 19:58:15' ) as (numitems int, region int);
That's one way. Another way would be for the function to use OUT
parameters (if you're running 8.1) or to return a custom type so
you could omit the column definition list. Then the query would be:
SELECT *
FROM sp_frontalerts_summary(1, '2006-07-27 18:08:09', '2006-07-27 19:58:15');
> So there isn't a question on that. My question is, how do I return a
> "setof record" back to the application.
The function and how you're calling it look correct except that
count() returns bigint, not int. You didn't mention what error
you're getting but I'd guess it's "wrong record type supplied in
RETURN NEXT". Try using "numitems bigint" instead of "numitems int".
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: return setof records
@ 2006-07-28 18:01 Chris Lukenbill <chris@blendinteractive.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Chris Lukenbill @ 2006-07-28 18:01 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
I think that actually solved the problem. The fact that I was sending back a
bigint. One of those things that's hard to spot when I don't know if I was
on the right track to begin with. Thank you everyone for your help.
Chris
On 7/28/06, Michael Fuhr <mike@fuhr.org> wrote:
>
> On Thu, Jul 27, 2006 at 03:41:31PM -0500, Chris Lukenbill wrote:
> > Everywhere I've looked the agreement was that making a call to the
> > function had to be done as follows:
> >
> > SELECT * FROM sp_frontalerts_summary(1,'2006-07-27 18:08:09','2006-07-27
> > 19:58:15' ) as (numitems int, region int);
>
> That's one way. Another way would be for the function to use OUT
> parameters (if you're running 8.1) or to return a custom type so
> you could omit the column definition list. Then the query would be:
>
> SELECT *
> FROM sp_frontalerts_summary(1, '2006-07-27 18:08:09', '2006-07-27
> 19:58:15');
>
> > So there isn't a question on that. My question is, how do I return a
> > "setof record" back to the application.
>
> The function and how you're calling it look correct except that
> count() returns bigint, not int. You didn't mention what error
> you're getting but I'd guess it's "wrong record type supplied in
> RETURN NEXT". Try using "numitems bigint" instead of "numitems int".
>
> --
> Michael Fuhr
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-07-28 18:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-27 20:41 return setof records Chris Lukenbill <chris@blendinteractive.com>
2006-07-28 14:13 ` Michael Fuhr <mike@fuhr.org>
2006-07-28 18:01 ` Chris Lukenbill <chris@blendinteractive.com>
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