Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D9B3F9FB856 for ; Tue, 26 Jun 2007 11:37:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 00194-07 for ; Tue, 26 Jun 2007 11:37:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id 410A69FB3D7 for ; Tue, 26 Jun 2007 11:37:13 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Tue, 26 Jun 2007 16:21:44 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Tue, 26 Jun 2007 16:37:10 +0200 Message-Id: <468140AB.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Tue, 26 Jun 2007 16:36:59 +0200 From: "Bart Degryse" To: Subject: Re: NO DATA FOUND Exception References: <36BBC5B2-CF22-46C1-A20C-36342160B693@seespotcode.net><051601c7b 7fd$e47c51b0$8f01010a@iptel.com.ar> In-Reply-To: <051601c7b7fd$e47c51b0$8f01010a@iptel.com.ar> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part1037DA9B.0__=" X-imss-version: 2.047 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/220 X-Sequence-Number: 28710 --=__Part1037DA9B.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable In case you would like to use set returning functions... =20 if your function will return records with the same structure as an = existing table CREATE FUNCTION my_func() RETURNS SETOF my_table AS ... =20 if not you have to define the returning type CREATE TYPE func_row AS ("field1" varchar(10), "field2" integer, "field3" = integer, ...) CREATE FUNCTION my_func() RETURNS SETOF func_row AS ... now you can use your function SELECT * FROM my_func(); =20 or =20 SELECT A.field1, A.field2 FROM my_func() A left join my_func() B on A.field2 =3D B.field3 WHERE A.field1 like 'B%'; >>> "Fernando Hevia" 2007-06-26 16:25 >>> On Jun 25, 2007, at 17:05, Michael Glaesemann wrote: >[Please create a new message to post about a new topic, rather than =20 >replying to and changing the subject of a previous message. This will =20 >allow mail clients which understand the References: header to =20 >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 =20 >(i.e., many rows), rather than just a single row. You'll want to look =20 >at set returning functions. One approach (probably not the best) =20 >would be to expand p_line into all of the possible v_search items and =20 >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.=20 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 *=20 INTO v_row FROM table1 WHERE ; 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 --=__Part1037DA9B.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

In case you would like to use set returning functions...

if your function will return records with the same structure as an = existing table

CREATE FUNCTION my_func() RETURNS SETOF my_table AS ...

if not you have to define the returning type

CREATE TYPE func_row AS ("field1" varchar(10), "field2" integer, = "field3" integer, ...)

CREATE FUNCTION my_func() RETURNS SETOF func_row AS ...

now you can use your function

SELECT * FROM my_func();

or

SELECT A.field1, A.field2

FROM my_func() A left join my_func() B on A.field2 =3D B.field3

WHERE A.field1 like 'B%';

>>> "Fernando Hevia" <fhevia@ip-tel.com.ar> = 2007-06-26 16:25 >>>

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
elegan= t.

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%ROW= TYPE;
BEGIN

SELECT *
INTO v_row
FROM = table1
WHERE <condition> ;

IF FOUND = THEN
     RETURN v_row;
END IF;

&nb= sp; RETURN NULL;

END;
$body$
LANGUAGE 'plpgsql';

SE= LECT my_func();
         &n= bsp;        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,
Fernand= o.

---------------------------(end of broadcast)----------------= -----------
TIP 7: You can help support the PostgreSQL project by = donating at

         &n= bsp;      http://www.postgresql.org/about/donate

--=__Part1037DA9B.0__=--