Functionally it's the same. The difference is that you don't have to DECLARE a variable for assembling your return value.
It's either
func(intext IN text, outtext OUT text .... returns NULL AS (
BEGIN
...
return;
END;
);
or
func(intext IN text) returns text AS (
DECLARE
outtext text;
BEGIN
...
return outtext;
END;
);
I find it especially handy when I want the function to return multiple values (thus a record) which are not of a table type.
The second way you would have to define a type for your return values first and than use it in your function
"My" way you would just write
func(intext IN text, outfield1 OUT text, outfield2 OUT date, outfield3 OUT integer) returns NULL AS (...);
A third way is to define the return value(s) inside the function as type record but than you have to name the output when you call the function
func(intext IN text) returns record AS (
DECLARE
rec record;
BEGIN
...
return rec;
END;
);
SELECT * FROM func('my input text') AS (field1 text, field2 date, field3 integer);
I find this less intuitive.
Buth in the end you just choose the technique you like best. I'm not aware of any performance penalties for either technique. Anyone?
>>> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> 2007-11-23 18:06 >>>
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote:
> I would do something like this (not tested, but conceptually working):
>
> CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text) AS
> $body$
> BEGIN
> cleantext = translate(webtext, E'\x92\x96', '''-');
> cleantext = regexp_replace(cleantext, E'\x9c', 'oe', 'g');
> cleantext = regexp_replace(cleantext, E'\x85', '...', 'g');
> END;
> $body$
> LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT;
Hi,
I was curious as to why you created this function with a prototype of
func(intext IN text, outtext OUT text) ... returns NULL
instead of the usual
func(intext text) ... returns TEXT
Is that a more efficient way?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster