From: | "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar> |
---|---|
To: | "'Michael Glaesemann'" <grzm(at)seespotcode(dot)net> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: NO DATA FOUND Exception |
Date: | 2007-06-26 14:25:49 |
Message-ID: | 051601c7b7fd$e47c51b0$8f01010a@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gurjeet Singh | 2007-06-26 14:26:00 | Re: a JOIN on same table, but 'slided over' |
Previous Message | Alvaro Herrera | 2007-06-26 14:21:42 | Re: LC_CTYPE and matching accented chars |
From | Date | Subject | |
---|---|---|---|
Next Message | Bart Degryse | 2007-06-26 14:36:59 | Re: NO DATA FOUND Exception |
Previous Message | Michael Glaesemann | 2007-06-26 12:56:30 | Re: yet another simple SQL question |
From | Date | Subject | |
---|---|---|---|
Next Message | Bart Degryse | 2007-06-26 14:36:59 | Re: NO DATA FOUND Exception |
Previous Message | Michael Glaesemann | 2007-06-26 12:56:30 | Re: yet another simple SQL question |