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. ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate