Thread: plpgsql return setof integer?

Greetings,

 I've got a fairly simple function that I'm trying to return a set, but
seem to have come across a stumbling block. When I execute the function
below (the input params are not used at the moment) I get this:

chris=# select htdig('foo', 'foo');
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "htdig" line 14 at return next


CREATE OR REPLACE FUNCTION htdig(text, text) RETURNS SETOF integer AS '
DECLARE
  result text[];
  low integer;
  high integer;
  item integer;
BEGIN
    result := htsearch(''sample_return.txt'',''dbname'');
    low  := 2;
    high := array_upper(result, 1);

    FOR i IN low..high LOOP
      item := result[i];
      RETURN NEXT item;
    END LOOP;
  RETURN;
END;
' LANGUAGE 'plpgsql' STABLE STRICT;


The function htsearch is working as expected (other than a strange HASH
as the first element, but I've compensated for that by starting at array
index 2). This is what it outputs:

chris=# select htsearch('sample_return2.txt','dbname');
             htsearch
----------------------------------
 {HASH(0x835c298),2100,2113,2114}
(1 row)

Any obvious thing that I'm doing wrong? I'm using 7.4RC2. As always, any
help or info would be much appreciated. Bonus points if someone knows
what the HASH is. :-)

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017

On Wed, Nov 12, 2003 at 05:35:40PM -0500, Christopher Murtagh wrote:

> chris=# select htsearch('sample_return2.txt','dbname');
>              htsearch
> ----------------------------------
>  {HASH(0x835c298),2100,2113,2114}
> (1 row)
>
> Any obvious thing that I'm doing wrong? I'm using 7.4RC2. As always, any
> help or info would be much appreciated. Bonus points if someone knows
> what the HASH is. :-)

You are initializing the array incorrectly in your htdig(text, text)
function.  {} is the hash reference you are seeing; HASH(0xwhatever) is
its text representation.  Do

 my @Result = ();

instead, or leave it uninitialized.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Un poeta es un mundo encerrado en un hombre" (Victor Hugo)

On Wed, 2003-11-12 at 22:57, Alvaro Herrera wrote:
> You are initializing the array incorrectly in your htdig(text, text)
> function.  {} is the hash reference you are seeing; HASH(0xwhatever) is
> its text representation.  Do
>
>  my @Result = ();
>
> instead, or leave it uninitialized.

 Thanks once again. You've really helped a lot on this. I especially
liked your 'return qq/{"/ . (join qq/","/, @_) . qq/"}/;' code. If you
were in Montreal, I would owe you a dinner or at least a coffee and a
big thanks. If you ever come this way, please be sure to give me a call.
Thanks again!

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017

Hey Christopher,

On Wed, Nov 12, 2003 at 11:42:56PM -0500, Christopher Murtagh wrote:
> On Wed, 2003-11-12 at 22:57, Alvaro Herrera wrote:
> > You are initializing the array incorrectly in your htdig(text, text)
> > function.  {} is the hash reference you are seeing; HASH(0xwhatever) is
> > its text representation.  Do
> >
> >  my @Result = ();
> >
> > instead, or leave it uninitialized.
>
>  Thanks once again. You've really helped a lot on this. I especially
> liked your 'return qq/{"/ . (join qq/","/, @_) . qq/"}/;' code. If you
> were in Montreal, I would owe you a dinner or at least a coffee and a
> big thanks. If you ever come this way, please be sure to give me a call.

I didn't really expect to remember this two years later :-), but I
was going over my old mail in order to delete the trash that is usually
left behind.  So if you still have that coffee around, there's news
that you'll be soon able to get rid of it: I'll be visiting McGill Uni
or thereabouts, for a Slony-II meeting.  Maybe I can relieve you of it
then ;-)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La soledad es compañía"

On Wed, 24 Aug 2005, Alvaro Herrera wrote:
> On Wed, Nov 12, 2003 at 11:42:56PM -0500, Christopher Murtagh wrote:
>>  Thanks once again. You've really helped a lot on this. I especially
>> liked your 'return qq/{"/ . (join qq/","/, @_) . qq/"}/;' code. If you
>> were in Montreal, I would owe you a dinner or at least a coffee and a
>> big thanks. If you ever come this way, please be sure to give me a call.
>
> I didn't really expect to remember this two years later :-), but I
> was going over my old mail in order to delete the trash that is usually
> left behind.  So if you still have that coffee around, there's news
> that you'll be soon able to get rid of it: I'll be visiting McGill Uni
> or thereabouts, for a Slony-II meeting.  Maybe I can relieve you of it
> then ;-)

  Very cool. Please let me know when you'll be by and I'd be happy to show
you around, and at the very least buy you that coffee. :-)

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017