Are postgres function arguments passed by reference or value?

Question

When I call a function and pass it an argument, is that argument's value actually copied in memory so that the called function receives a copy of the value, or it is passed a reference/pointer?

I guess in some cases it's obvious - I'd expect an integer to be copied, for example, but what if I have a large string or array or table row?

From my own observations it appears the value is always copied, because I can edit the value in the called function without changing the value in the calling function. In fact I can't think of any case where I've been able to modify something passed in as an argument, and have the calling function pick up the change without returning the changed data from the called function.

I'm always uneasy about passing large amounts of data from one function to another for this reason. I haven't been able to find any documentation on how postgres handles arguments, hence this question. The function types I use are SQL and plpgsql.

Laurenz Albe · Accepted Answer · 2018-05-02 14:43:55Z

6

It depends on the procedural language and the type.

C functions:

Fixed length data types are passed by value if the typbyval column in pg_type is TRUE.
Unless you configure PostgreSQL with --disable-float4-byval, real is passed by value.
bigint, double precision and the timestamp types are passed by value on 64-bit architectures unless you configure PostgreSQL with --disable-float8-byval.

All other data types are passed by reference, and it is your responsibility not to modify the data (see the documentation and the source in configure.in and src/include/postgres.h).

PL/pgSQL functions:

All arguments are passed by value, because the following code in function plpgsql_exec_function in src/pl/plpgsql/src/pl_exec.c creates a copy:

/*
 * Make local execution copies of all the datums
 */
estate.err_text = gettext_noop("during initialization of execution state");
copy_plpgsql_datums(&estate, func);

I didn't explicitly check the other procedural languages, but I'm quite certain that everything will be passed by value as well, since that data have to be converted to the internal representation of the respective programming language.

edited May 2, 2018 at 14:43

answered May 2, 2018 at 11:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

404 Over a year ago

Thanks. Can I ask how you know this? Is it documented somewhere, or a conclusion from reading the source code, or something else?

Laurenz Albe Over a year ago

Ok, I checked, and it turns out that the answer is more complicated.

mleko Over a year ago

@LaurenzAlbe aren't functions exposed in c,(postgresql.org/docs/current/xfunc-c.html#XFUNC-C-BASETYPE) and called in pgsql still use pass by value?

mleko Over a year ago

also, if we return records in pgsql, the records are also copied for return?

Laurenz Albe Over a year ago

@mleko That's a different question.

Collectives™ on Stack Overflow

Are postgres function arguments passed by reference or value?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related