From 3f8db37c2f1eeeffd9dae3189b783a463f56fe77 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 12 Aug 2006 20:05:56 +0000 Subject: Tweak SPI_cursor_open to allow INSERT/UPDATE/DELETE RETURNING; this was merely a matter of fixing the error check, since the underlying Portal infrastructure already handles it. This in turn allows these statements to be used in some existing plpgsql and plperl contexts, such as a plpgsql FOR loop. Also, do some marginal code cleanup in places that were being sloppy about distinguishing SELECT from SELECT INTO. --- doc/src/sgml/plperl.sgml | 52 ++++++++++++++++++----------------------------- doc/src/sgml/plpgsql.sgml | 27 +++++++++++++++--------- doc/src/sgml/spi.sgml | 28 +++++++++++++------------ 3 files changed, 52 insertions(+), 55 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 12ac0157c3e..32a7b12c710 100644 --- a/doc/src/sgml/plperl.sgml +++ b/doc/src/sgml/plperl.sgml @@ -1,4 +1,4 @@ - + PL/Perl - Perl Procedural Language @@ -244,18 +244,8 @@ $$ LANGUAGE plperl; SELECT * FROM perl_set(); - - - PL/Perl does not currently have full support for - domain types: it treats a domain the same as the underlying scalar - type. This means that constraints associated with the domain will - not be enforced. This is not an issue for function arguments, but - it is a hazard if you declare a PL/Perl function - as returning a domain type. - - If you wish to use the strict pragma with your code, the easiest way to do so is to SET @@ -439,26 +429,26 @@ SELECT * from lotsa_md5(500); The advantage of prepared queries is that is it possible to use one prepared plan for more - than one query execution. After the plan is not needed anymore, it must be freed with + than one query execution. After the plan is not needed anymore, it may be freed with spi_freeplan: CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$ - $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL'); + $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL'); $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$ - return spi_exec_prepared( - $_SHARED{my_plan}, - $_[0], - )->{rows}->[0]->{now}; + return spi_exec_prepared( + $_SHARED{my_plan}, + $_[0], + )->{rows}->[0]->{now}; $$ LANGUAGE plperl; CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$ - spi_freeplan( $_SHARED{my_plan}); - undef $_SHARED{my_plan}; + spi_freeplan( $_SHARED{my_plan}); + undef $_SHARED{my_plan}; $$ LANGUAGE plperl; SELECT init(); @@ -478,16 +468,14 @@ SELECT done(); - spi_cursor_close can be used to abort sequence of - spi_fetchrow calls. Normally, the call to - spi_fetchrow that returns undef is - the signal that there are no more rows to read. Also - that call automatically frees the cursor associated with the query. If it is desired not - to read all retuned rows, spi_cursor_close must be - called to avoid memory leaks. + Normally, spi_fetchrow should be repeated until it + returns undef, indicating that there are no more + rows to read. The cursor is automatically freed when + spi_fetchrow returns undef. + If you do not wish to read all the rows, instead call + spi_cursor_close to free the cursor. + Failure to do so will result in memory leaks. - - @@ -630,8 +618,8 @@ CREATE FUNCTION badfunc() RETURNS integer AS $$ return 1; $$ LANGUAGE plperl; - The creation of this function will fail as its use of a forbidden - operation will be be caught by the validator. + The creation of this function will fail as its use of a forbidden + operation will be be caught by the validator. @@ -748,8 +736,8 @@ $$ LANGUAGE plperl; Name of the table on which the trigger fired. This has been deprecated, - and could be removed in a future release. - Please use $_TD->{table_name} instead. + and could be removed in a future release. + Please use $_TD->{table_name} instead. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index ac5b2b4cfc6..fb2fe735a6c 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -2040,9 +2040,8 @@ END LOOP label ; The target is a record variable, row variable, or comma-separated list of scalar variables. The target is successively assigned each row - resulting from the query (which must be a - SELECT command) and the loop body is executed for each - row. Here is an example: + resulting from the query and the loop body is + executed for each row. Here is an example: CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$ DECLARE @@ -2069,6 +2068,15 @@ $$ LANGUAGE plpgsql; assigned row value is still accessible after the loop. + + The query used in this type of FOR + statement can be any query that returns rows to the caller: + SELECT (without INTO) is the most common case, + but you can also use INSERT, UPDATE, or + DELETE with a RETURNING clause. Some utility + commands such as EXPLAIN will work too. + + The FOR-IN-EXECUTE statement is another way to iterate over rows: @@ -2078,12 +2086,11 @@ FOR target IN EXECUTE text_expressionstatements END LOOP label ; - This is like the previous form, except that the source - SELECT statement is specified as a string - expression, which is evaluated and replanned on each entry to - the FOR loop. This allows the programmer to choose the speed of - a preplanned query or the flexibility of a dynamic query, just - as with a plain EXECUTE statement. + This is like the previous form, except that the source query + is specified as a string expression, which is evaluated and replanned + on each entry to the FOR loop. This allows the programmer to + choose the speed of a preplanned query or the flexibility of a dynamic + query, just as with a plain EXECUTE statement. diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index 4430b3e4c71..016874dce70 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -1,4 +1,4 @@ - + Server Programming Interface @@ -535,15 +535,15 @@ typedef struct SPI_ERROR_TRANSACTION - if any command involving transaction manipulation was attempted - (BEGIN, - COMMIT, - ROLLBACK, - SAVEPOINT, - PREPARE TRANSACTION, - COMMIT PREPARED, - ROLLBACK PREPARED, - or any variant thereof) + if any command involving transaction manipulation was attempted + (BEGIN, + COMMIT, + ROLLBACK, + SAVEPOINT, + PREPARE TRANSACTION, + COMMIT PREPARED, + ROLLBACK PREPARED, + or any variant thereof) @@ -917,10 +917,12 @@ bool SPI_is_cursor_plan(void * plan) SPI_is_cursor_plan returns true if a plan prepared by SPI_prepare can be passed - as an argument to SPI_cursor_open and - false if that is not the case. The criteria are that the + as an argument to SPI_cursor_open, or + false if that is not the case. The criteria are that the plan represents one single command and that this - command is a SELECT without an INTO + command returns tuples to the caller; for example, SELECT + is allowed unless it contains an INTO clause, and + UPDATE is allowed only if it contains a RETURNING clause. -- cgit v1.2.3