Using a RETURN NEXT

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Using a RETURN NEXT
4+ messages / 3 participants
[nested] [flat]

* Using a RETURN NEXT
@ 2003-06-06 22:14 Mr Weinbach, Larry <larryweinbach@yahoo.com>
  2003-06-08 22:33 ` Re: Using a RETURN NEXT Joe Conway <mail@joeconway.com>
  2003-06-09 11:22 ` Re: Using a RETURN NEXT Mendola Gaetano <mendola@bigfoot.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Mr Weinbach, Larry @ 2003-06-06 22:14 UTC (permalink / raw)
  To: pgsql-sql

Hi all,

I found this example from Postgres site

create or replace function GetRows(text) returns setof
record as
'
declare
    r record;
begin
    for r in EXECUTE ''select * from '' || $1 loop
        return next r;
    end loop;
    return;
end
'
language 'plpgsql';


I am trying to use the same idea but in this way ..

CREATE OR REPLACE FUNCTION word_case() RETURNS setof
RECORD AS'
DECLARE
  reg record;
  BEGIN
     FOR reg IN SELECT message FROM rtable LOOP
            RETURN next reg;
     END LOOP;
    RETURN;
  END;
' LANGUAGE 'plpgsql';

But at execution time I am getting thi error :

WARNING:  Error occurred while executing PL/pgSQL
function word_case
WARNING:  line 5 at return next
ERROR:  Set-valued function called in context that
cannot accept a set

I also tried using my own type defined but I got the
same error.

Any hint or idea will be appreciated ...

Regards

Larry

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Using a RETURN NEXT
  2003-06-06 22:14 Using a RETURN NEXT Mr Weinbach, Larry <larryweinbach@yahoo.com>
@ 2003-06-08 22:33 ` Joe Conway <mail@joeconway.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Joe Conway @ 2003-06-08 22:33 UTC (permalink / raw)
  To: Mr Weinbach, Larry <larryweinbach@yahoo.com>; +Cc: pgsql-sql

Mr Weinbach, Larry wrote:
> But at execution time I am getting thi error :
> 
> WARNING:  Error occurred while executing PL/pgSQL
> function word_case
> WARNING:  line 5 at return next
> ERROR:  Set-valued function called in context that
> cannot accept a set
> 

You didn't show the execution time SQL statement, but the error you are 
getting usually indicates you did something like:

   SELECT word_case();

but you should have done:

   (define the function to return setof record)
   SELECT * FROM word_case() AS (message text);

or

   CREATE TYPE word_case_type AS (message text);
   (define the function to return setof word_case_type)
   SELECT * FROM word_case();

HTH,

Joe




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Using a RETURN NEXT
  2003-06-06 22:14 Using a RETURN NEXT Mr Weinbach, Larry <larryweinbach@yahoo.com>
@ 2003-06-09 11:22 ` Mendola Gaetano <mendola@bigfoot.com>
  2003-06-10 21:01   ` Re: Using a RETURN NEXT Mr Weinbach, Larry <larryweinbach@yahoo.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Mendola Gaetano @ 2003-06-09 11:22 UTC (permalink / raw)
  To: Mr Weinbach, Larry <larryweinbach@yahoo.com>; pgsql-sql

From: "Mr Weinbach, Larry" <larryweinbach@yahoo.com>

> But at execution time I am getting thi error :
> 
> WARNING:  Error occurred while executing PL/pgSQL
> function word_case
> WARNING:  line 5 at return next
> ERROR:  Set-valued function called in context that
> cannot accept a set
> 
> I also tried using my own type defined but I got the
> same error.
> 
> Any hint or idea will be appreciated ...

I guess that you are calling that function in this way:
#select word_case();

that function is a "table function" so you should use it like a table:

#select * from word_case();


Regards
Gaetano Mendola











^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Using a RETURN NEXT
  2003-06-06 22:14 Using a RETURN NEXT Mr Weinbach, Larry <larryweinbach@yahoo.com>
  2003-06-09 11:22 ` Re: Using a RETURN NEXT Mendola Gaetano <mendola@bigfoot.com>
@ 2003-06-10 21:01   ` Mr Weinbach, Larry <larryweinbach@yahoo.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Mr Weinbach, Larry @ 2003-06-10 21:01 UTC (permalink / raw)
  To: pgsql-sql

Thanks a lot Gaetano, Joe

Now, it is working.

 --- Mendola Gaetano <mendola@bigfoot.com> escribió: >
From: "Mr Weinbach, Larry" <larryweinbach@yahoo.com>
> 
> > But at execution time I am getting thi error :
> > 
> > WARNING:  Error occurred while executing PL/pgSQL
> > function word_case
> > WARNING:  line 5 at return next
> > ERROR:  Set-valued function called in context that
> > cannot accept a set
> > 
> > I also tried using my own type defined but I got
> the
> > same error.
> > 
> > Any hint or idea will be appreciated ...
> 
> I guess that you are calling that function in this
> way:
> #select word_case();
> 
> that function is a "table function" so you should
> use it like a table:
> 
> #select * from word_case();
> 
> 
> Regards
> Gaetano Mendola
> 
> 
> 
> 
> 
> 
> 
>  

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2003-06-10 21:01 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-06 22:14 Using a RETURN NEXT Mr Weinbach, Larry <larryweinbach@yahoo.com>
2003-06-08 22:33 ` Joe Conway <mail@joeconway.com>
2003-06-09 11:22 ` Mendola Gaetano <mendola@bigfoot.com>
2003-06-10 21:01   ` Mr Weinbach, Larry <larryweinbach@yahoo.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