From 9048b73184b6852b71faf4481b75ab5850a9cd1b Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 22 Sep 2009 23:43:43 +0000 Subject: Implement the DO statement to support execution of PL code without having to create a function for it. Procedural languages now have an additional entry point, namely a function to execute an inline code block. This seemed a better design than trying to hide the transient-ness of the code from the PL. As of this patch, only plpgsql has an inline handler, but probably people will soon write handlers for the other standard PLs. In passing, remove the long-dead LANCOMPILER option of CREATE LANGUAGE. Petr Jelinek --- doc/src/sgml/catalogs.sgml | 20 +++++- doc/src/sgml/config.sgml | 17 ++++- doc/src/sgml/keywords.sgml | 18 ++--- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/create_language.sgml | 32 +++++++-- doc/src/sgml/ref/do.sgml | 122 ++++++++++++++++++++++++++++++++++ doc/src/sgml/reference.sgml | 3 +- doc/src/sgml/xplang.sgml | 35 ++++++++-- 8 files changed, 225 insertions(+), 25 deletions(-) create mode 100644 doc/src/sgml/ref/do.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 3f7fdf16b45..dd103573a5d 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -2941,6 +2941,18 @@ + + laninline + oid + pg_proc.oid + + This references a function that is responsible for executing + inline anonymous code blocks + ( blocks). + Zero if inline blocks are not supported + + + lanvalidator oid @@ -3547,6 +3559,12 @@ Name of call handler function + + tmplinline + text + Name of anonymous-block handler function, or NULL if none + + tmplvalidator text diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index fa2a12feab1..ee28bbb1079 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -3964,6 +3964,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + default_do_language (string) + + default_do_language configuration parameter + + + + This parameter specifies the language to use when the + LANGUAGE option is omitted in a + statement. + The default is plpgsql. + + + + transaction isolation level diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index cb167ce10b4..8bfede77d6f 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -1,4 +1,4 @@ - + <acronym>SQL</acronym> Key Words @@ -2375,6 +2375,14 @@ reserved reserved + + INLINE + non-reserved + + + + + INNER reserved (can be function or type) @@ -2575,14 +2583,6 @@ - - LANCOMPILER - non-reserved - - - - - LANGUAGE non-reserved diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 6c20b623c49..845033b6b66 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -77,6 +77,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/create_language.sgml b/doc/src/sgml/ref/create_language.sgml index ae02995e37f..4c0463ddec1 100644 --- a/doc/src/sgml/ref/create_language.sgml +++ b/doc/src/sgml/ref/create_language.sgml @@ -1,5 +1,5 @@ @@ -23,7 +23,7 @@ PostgreSQL documentation CREATE [ PROCEDURAL ] LANGUAGE name CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name - HANDLER call_handler [ VALIDATOR valfunction ] + HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ] @@ -133,7 +133,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name call_handler is the name of a previously registered function that will be - called to execute the procedural language functions. The call + called to execute the procedural language's functions. The call handler for a procedural language must be written in a compiled language such as C with version 1 call convention and registered with PostgreSQL as a @@ -144,6 +144,27 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name + + INLINE inline_handler + + + + inline_handler is the + name of a previously registered function that will be called + to execute an anonymous code block + ( command) + in this language. + If no inline_handler + function is specified, the language does not support anonymous code + blocks. + The handler function must take one argument of + type internal, which will be the DO command's + internal representation, and it will typically return + void. The return value of the handler is ignored. + + + + VALIDATOR valfunction @@ -216,7 +237,8 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name - The call handler function and the validator function (if any) + The call handler function, the inline handler function (if any), + and the validator function (if any) must already exist if the server does not have an entry for the language in pg_pltemplate. But when there is an entry, the functions need not already exist; @@ -230,7 +252,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE namePostgreSQL versions before 7.3, it was necessary to declare handler functions as returning the placeholder type opaque, rather than language_handler. - To support loading + To support loading of old dump files, CREATE LANGUAGE will accept a function declared as returning opaque, but it will issue a notice and change the function's declared return type to language_handler. diff --git a/doc/src/sgml/ref/do.sgml b/doc/src/sgml/ref/do.sgml new file mode 100644 index 00000000000..2fb53806630 --- /dev/null +++ b/doc/src/sgml/ref/do.sgml @@ -0,0 +1,122 @@ + + + + + DO + 7 + SQL - Language Statements + + + + DO + execute an anonymous code block + + + + DO + + + + anonymous code blocks + + + + +DO code [ LANGUAGE lang_name ] + + + + + Description + + + DO executes an anonymous code block, or in other + words a transient anonymous function in a procedural language. + + + + The code block is treated as though it were the body of a function + with no parameters, returning void. It is parsed and + executed a single time. + + + + + Parameters + + + + code + + + The procedural language code to be executed. This must be specified + as a string literal, just as in CREATE FUNCTION. + Use of a dollar-quoted literal is recommended. + + + + + + lang_name + + + The name of the procedural language the code is written in. + If omitted, the default is determined by the runtime parameter + . + + + + + + + + Notes + + + The procedural language to be used must already have been installed + into the current database by means of CREATE LANGUAGE. + + + + The user must have USAGE privilege for the procedural + language, or must be a superuser if the language is untrusted. + This is the same privilege requirement as for creating a function + in the language. + + + + + Examples + + Execute a simple PL/pgsql loop without needing to create a function: + +DO $$ +DECLARE r record; +BEGIN + FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno + LOOP + RAISE NOTICE '%, %', r.roomno, r.comment; + END LOOP; +END$$; + + + + + Compatibility + + + There is no DO statement in the SQL standard. + + + + + See Also + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index d3a862959d9..48f8040541d 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,4 +1,4 @@ - + Reference @@ -105,6 +105,7 @@ &declare; &delete; &discard; + &do; &dropAggregate; &dropCast; &dropConversion; diff --git a/doc/src/sgml/xplang.sgml b/doc/src/sgml/xplang.sgml index 9882d835e35..b48b78f95ba 100644 --- a/doc/src/sgml/xplang.sgml +++ b/doc/src/sgml/xplang.sgml @@ -1,4 +1,4 @@ - + Procedural Languages @@ -75,9 +75,9 @@ createlang plpgsql template1 - A procedural language is installed in a database in four steps, + A procedural language is installed in a database in five steps, which must be carried out by a database superuser. (For languages - known to CREATE LANGUAGE, the second and third steps + known to CREATE LANGUAGE, the second through fourth steps can be omitted, because they will be carried out automatically if needed.) @@ -110,12 +110,28 @@ CREATE FUNCTION handler_function_name() + + Optionally, the language handler can provide an inline + handler function that executes anonymous code blocks + ( commands) + written in this language. If an inline handler function + is provided by the language, declare it with a command like + +CREATE FUNCTION inline_function_name(internal) + RETURNS void + AS 'path-to-shared-object' + LANGUAGE C; + + + + + Optionally, the language handler can provide a validator function that checks a function definition for correctness without actually executing it. The validator function is called by CREATE FUNCTION if it exists. If a validator function - is provided by the handler, declare it with a command like + is provided by the language, declare it with a command like CREATE FUNCTION validator_function_name(oid) RETURNS void @@ -125,12 +141,13 @@ CREATE FUNCTION validator_function_name(oid) - + The PL must be declared with the command CREATE TRUSTED PROCEDURAL LANGUAGE language-name HANDLER handler_function_name + INLINE inline_function_name VALIDATOR validator_function_name ; The optional key word TRUSTED specifies that @@ -173,10 +190,13 @@ CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS - PL/pgSQL has a validator function, - so we declare that too: + PL/pgSQL has an inline handler function + and a validator function, so we declare those too: +CREATE FUNCTION plpgsql_inline_handler(internal) RETURNS void AS + '$libdir/plpgsql' LANGUAGE C; + CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS '$libdir/plpgsql' LANGUAGE C; @@ -187,6 +207,7 @@ CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler + INLINE plpgsql_inline_handler VALIDATOR plpgsql_validator; then defines that the previously declared functions -- cgit v1.2.3