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): > =20 > CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT = text) AS > $body$ > BEGIN > cleantext =3D translate(webtext, E'\x92\x96', '''-'); > cleantext =3D regexp_replace(cleantext, E'\x9c', 'oe', 'g'); > cleantext =3D regexp_replace(cleantext, E'\x85', '...', 'g'); > END; > $body$ > LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT; Hi,=20 I was curious as to why you created this function with a prototype of=20 func(intext IN text, outtext OUT text) ... returns NULL instead of the usual func(intext text) ... returns TEXT=20 Is that a more efficient way? ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster --=__PartD7F1D732.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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@apa= rtia.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 =3D translate(webtext, = E'\x92\x96', '''-');
> cleantext =3D regexp_replace(cleantext, = E'\x9c', 'oe', 'g');
> cleantext =3D 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

--=__PartD7F1D732.0__=--