postgresql.git/doc/src/sgml/ref/create_function.sgml, branch master

doc: consistently use "structname" and "structfield" markup

2025-11-08T14:49:43+00:00

Previously "literal" and "classname" were used, inconsistently, for SQL table and column names. Reported-by: Peter Smith Author: Peter Smith Discussion: https://postgr.es/m/CAHut+Pvtf24r+bdPgBind84dBLPvgNL7aB+=HxAUupdPuo2gRg@mail.gmail.com Backpatch-through: master

Doc: use uppercase keywords in SQLs

2025-11-06T03:03:02+00:00

Use uppercase SQL keywords consistently throughout the documentation to ease reading. Also add whitespace in a couple of places where it improves readability. Author: Erik Wienhold Reviewed-by: David Rowley Discussion: https://postgr.es/m/82eb512b-8ed2-46be-b311-54ffd26978c4%40ewie.name

Allow "internal" subtransactions in parallel mode.

2024-03-28T16:43:10+00:00

Allow use of BeginInternalSubTransaction() in parallel mode, so long as the subtransaction doesn't attempt to acquire an XID or increment the command counter. Given those restrictions, the other parallel processes don't need to know about the subtransaction at all, so this should be safe. The benefit is that it allows subtransactions intended for error recovery, such as pl/pgsql exception blocks, to be used in PARALLEL SAFE functions. Another reason for doing this is that the API of BeginInternalSubTransaction() doesn't allow reporting failure. pl/python for one, and perhaps other PLs, copes very poorly with an error longjmp out of BeginInternalSubTransaction(). The headline feature of this patch removes the only easily-triggerable failure case within that function. There remain some resource-exhaustion and similar cases, which we now deal with by promoting them to FATAL errors, so that callers need not try to clean up. (It is likely that such errors would leave us with corrupted transaction state inside xact.c, making recovery difficult if not impossible anyway.) Although this work started because of a report of a pl/python crash, we're not going to do anything about that in the back branches. Back-patching this particular fix is obviously not very wise. While we could contemplate some narrower band-aid, pl/python is already an untrusted language, so it seems okay to classify this as a "so don't do that" case. Patch by me, per report from Hao Zhang. Thanks to Robert Haas for review. Discussion: https://postgr.es/m/CALY6Dr-2yLVeVPhNMhuBnRgOZo1UjoTETgtKBx1B2gUi8yy+3g@mail.gmail.com

Assorted improvements to SECURITY DEFINER functions documentation.

2023-01-16T15:49:59+00:00

Add a cross-reference from the part of the page that introdues SECURITY INVOKER and SECURITY DEFINER to the part of the page that talks about writing SECURITY DEFINER functions safely, so that users are less likely to miss it. Remove discussion of the pre-8.3 behavior on the theory that it's probably not very relevant any more, that release having gone out of support nearly a decade ago. Add a mention of the new createrole_self_grant GUC, which in certain cases might need to be set to a safe value to avoid unexpected consequences. Possibly this section needs major surgery rather than just these small tweaks, but hopefully this is at least a small step forward. Discussion: http://postgr.es/m/CA+Tgmoauqd1cHQjsNEoxL5O-kEO4iC9dAPyCudSvmNqPJGmy9g@mail.gmail.com

revert "warn of SECURITY DEFINER schemas for non-sql_body funcs"

2022-09-28T17:05:21+00:00

doc revert of commit 1703726488. Change was applied to irrelevant branches, and was not detailed enough to be helpful in relevant branches. Reported-by: Peter Eisentraut, Noah Misch Discussion: https://postgr.es/m/a2dc9de4-24fc-3222-87d3-0def8057d7d8@enterprisedb.com Backpatch-through: 10

doc: warn of SECURITY DEFINER schemas for non-sql_body functions

2022-09-01T01:10:37+00:00

Non-sql_body functions are evaluated at runtime. Reported-by: Erki Eessaar Discussion: https://postgr.es/m/AM9PR01MB8268BF5E74E119828251FD34FE409@AM9PR01MB8268.eurprd01.prod.exchangelabs.com Backpatch-through: 10

Doc: fix confusion about LEAKPROOF in syntax summaries.

2021-06-23T18:27:13+00:00

The syntax summaries for CREATE FUNCTION and allied commands made it look like LEAKPROOF is an alternative to IMMUTABLE/STABLE/VOLATILE, when of course it is an orthogonal option. Improve that. Per gripe from aazamrafeeque0. Thanks to David Johnston for suggestions. Discussion: https://postgr.es/m/162444349581.694.5818572718530259025@wrigleys.postgresql.org

Use the correct article for abbreviations

2021-06-11T01:38:04+00:00

We've accumulated quite a mix of instances of "an SQL" and "a SQL" in the documents. It would be good to be a bit more consistent with these. The most recent version of the SQL standard I looked at seems to prefer "an SQL". That seems like a good lead to follow, so here we change all instances of "a SQL" to become "an SQL". Most instances correctly use "an SQL" already, so it also makes sense to use the dominant variation in order to minimise churn. Additionally, there were some other abbreviations that needed to be adjusted. FSM, SSPI, SRF and a few others. Also fix some pronounceable, abbreviations to use "a" instead of "an". For example, "a SASL" instead of "an SASL". Here I've only adjusted the documents and error messages. Many others still exist in source code comments. Translator hint comments seem to be the biggest culprit. It currently does not seem worth the churn to change these. Discussion: https://postgr.es/m/CAApHDvpML27UqFXnrYO1MJddsKVMQoiZisPvsAGhKE_tsKXquw%40mail.gmail.com

SQL-standard function body

2021-04-07T19:47:55+00:00

This adds support for writing CREATE FUNCTION and CREATE PROCEDURE statements for language SQL with a function body that conforms to the SQL standard and is portable to other implementations. Instead of the PostgreSQL-specific AS $$ string literal $$ syntax, this allows writing out the SQL statements making up the body unquoted, either as a single statement: CREATE FUNCTION add(a integer, b integer) RETURNS integer LANGUAGE SQL RETURN a + b; or as a block CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL BEGIN ATOMIC INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); END; The function body is parsed at function definition time and stored as expression nodes in a new pg_proc column prosqlbody. So at run time, no further parsing is required. However, this form does not support polymorphic arguments, because there is no more parse analysis done at call time. Dependencies between the function and the objects it uses are fully tracked. A new RETURN statement is introduced. This can only be used inside function bodies. Internally, it is treated much like a SELECT statement. psql needs some new intelligence to keep track of function body boundaries so that it doesn't send off statements when it sees semicolons that are inside a function body. Tested-by: Jaime Casanova Reviewed-by: Julien Rouhaud Discussion: https://www.postgresql.org/message-id/flat/1c11f1eb-f00c-43b7-799d-2d44132c02d7@2ndquadrant.com

Doc: improve introductory information about procedures.

2021-03-10T16:33:50+00:00

Clarify the discussion in "User-Defined Procedures", by laying out the key differences between functions and procedures in a bulleted list. Notably, this avoids burying the lede about procedures being able to do transaction control. Make the back-link in the CREATE FUNCTION reference page more prominent, and add one in CREATE PROCEDURE. Per gripe from Guyren Howe. Thanks to David Johnston for discussion. Discussion: https://postgr.es/m/BYAPR03MB4903C53A8BB7EFF5EA289674A6949@BYAPR03MB4903.namprd03.prod.outlook.com