From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'Michael Glaesemann'" <grzm@seespotcode.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: NO DATA FOUND Exception
Date: Tue, 26 Jun 2007 11:25:49 -0300
Organization: IP-TEL
Message-ID: <051601c7b7fd$e47c51b0$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <36BBC5B2-CF22-46C1-A20C-36342160B693@seespotcode.net>
Thread-Index: Ace3ajZfuKlYOkZHSvuHZLaJuu6dIgAjYpwA


On Jun 25, 2007, at 17:05, Michael Glaesemann wrote:

>[Please create a new message to post about a new topic, rather than  
>replying to and changing the subject of a previous message. This will  
>allow mail clients which understand the References: header to  
>properly thread replies.]

Wasn't aware of this. Will do.
I should obtain a better mail client.

>However, it looks like you're trying to return a set of results  
>(i.e., many rows), rather than just a single row. You'll want to look  
>at set returning functions. One approach (probably not the best)  
>would be to expand p_line into all of the possible v_search items and  
>append that to your query, which would look something like:

Thank you for your help. All the advice was very useful and I have now a
working function. 
I still have an issue left: I would like my function to return multiple
values (as in columns of a row).
Actually I found two possibilities: array and record. I ended up using
arrays since I couldn't figure out how to access the record data from
outside the function. Nevertheless I think a solution based on returning a
record type when you actually want to return the whole row would be more
elegant.

For example:

CREATE TABLE table1 (
   field1 text,
   field2 text,
   field3 text
);

INSERT INTO table1 ('data1', 'data2', 'data3');

CREATE FUNCTION my_func() RETURNS record AS
$body$
DECLARE
  v_row table1%ROWTYPE;
BEGIN

  SELECT * 
  INTO v_row
  FROM table1
  WHERE <condition> ;

  IF FOUND THEN
     RETURN v_row;
  END IF;

  RETURN NULL;

END;
$body$
LANGUAGE 'plpgsql';


SELECT my_func();
                  my_func
---------------------------------------------------
(data1, data2, data3)

How do I refer a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?

It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.

Regards,
Fernando.