OUT parameter

[Daniel Caune]

Hi,

Is there any suggestion against using OUT parameter for local
calculation such as using a local variable?

CREATE OR REPLACE FUNCTION foo(a IN int,
b1 OUT int,
b2 OUT int)
AS $$
BEGIN
FOR (...) LOOP
b1 = (...);
b2 = (...);
END LOOP;
END;
$$ LANGUAGE PLPGSQL;

or for some reasons (performance or whatever other details of
implementation), would it be preferable to use local variable and to
initialize the OUT parameters at the end?

CREATE OR REPLACE FUNCTION foo(a IN int,
b1 OUT int,
b2 OUT int)
AS $$
V_b1 int;
V_b2 int;
BEGIN
FOR (...) LOOP
V_b1 = (...);
V_b2 = (...);
END LOOP;

b1 = V_b1;
b2 = V_b2;
END;
$$ LANGUAGE PLPGSQL;

Thanks,


--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Owen Jacobson]

Post by Daniel Caune
Is there any suggestion against using OUT parameter for local
calculation such as using a local variable?
CREATE OR REPLACE FUNCTION foo(a IN int,
b1 OUT int,
b2 OUT int)
AS $$
BEGIN
FOR (...) LOOP
b1 = (...);
b2 = (...);
END LOOP;
END;
$$ LANGUAGE PLPGSQL;

I'd say there's no problem with this, PROVIDED you can ensure you'll never abort before completing the computation. It's not a good idea to modify out parameters partway; programmers (myself included) have this nasty habit of assuming, rightly or wrongly, that a failed function call won't have destroyed the parameters.

If you can't ensure you'll always complete, use locals.

-Owen

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Tom Lane]

Post by Owen Jacobson

Post by Daniel Caune
Is there any suggestion against using OUT parameter for local
calculation such as using a local variable?

In plpgsql (at least in the current implementation) an OUT parameter is
pretty much just a local variable, and so there's no efficiency argument
against using it as a temporary. Whether you consider this good style
is a matter of opinion.

Post by Owen Jacobson
I'd say there's no problem with this, PROVIDED you can ensure you'll
never abort before completing the computation.

Not really an issue in Postgres: we do not support pass-by-reference
parameters and are unlikely to start doing so. There isn't any way
that you can affect locals of a calling procedure before you return.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Owen Jacobson]

Post by Tom Lane

Post by Owen Jacobson
I'd say there's no problem with this, PROVIDED you can ensure you'll
never abort before completing the computation.

Not really an issue in Postgres: we do not support pass-by-reference
parameters and are unlikely to start doing so. There isn't any way
that you can affect locals of a calling procedure before you return.

Then I've misunderstood the semantics of OUT and more importantly INOUT parameters. Thanks for the correction; I'm reading Daniel Caune's notes on the docs now.

-Owen

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Daniel Caune]

Post by Owen Jacobson

Post by Tom Lane

Post by Owen Jacobson
I'd say there's no problem with this, PROVIDED you can ensure

you'll

Post by Owen Jacobson

Post by Tom Lane

Post by Owen Jacobson
never abort before completing the computation.

Not really an issue in Postgres: we do not support pass-by-reference
parameters and are unlikely to start doing so. There isn't any way
that you can affect locals of a calling procedure before you return.

Then I've misunderstood the semantics of OUT and more importantly

INOUT

Post by Owen Jacobson
parameters. Thanks for the correction; I'm reading Daniel Caune's

notes

Post by Owen Jacobson
on the docs now.
-Owen

Funny! I started that thread on OUT parameter; that's a kind of
circle... :-)

--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match