Add support for an optional INTO clause to PL/PgSQL's EXECUTE command.

This allows the result of executing a SELECT to be assigned to a row
variable, record variable, or list of scalars. Docs and regression tests
updated. Per Pavel Stehule, improvements and cleanup by Neil Conway.

2 files changed, 67 insertions, 0 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 08fbe46b3a2..39e61e09cf2 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2380,3 +2380,38 @@ ERROR:  control reached end of function without RETURN
 CONTEXT:  PL/pgSQL function "missing_return_expr"
 drop function void_return_expr();
 drop function missing_return_expr();
+--
+-- EXECUTE ... INTO test
+--
+create table eifoo (i integer, y integer);
+create type eitype as (i integer, y integer);
+create or replace function execute_into_test(varchar) returns record as $$
+declare
+    _r record;
+    _rt eifoo%rowtype;
+    _v eitype;
+    i int;
+    j int;
+    k int;
+begin
+    execute 'insert into '||$1||' values(10,15)';
+    execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
+    raise notice '% %', _r.i, _r.y;
+    execute 'select * from '||$1||' limit 1' into _rt;
+    raise notice '% %', _rt.i, _rt.y;
+    execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
+    raise notice '% % %', i, j, k;
+    execute 'select 1,2' into _v;
+    return _v;
+end; $$ language plpgsql;
+select execute_into_test('eifoo');
+NOTICE:  10 1
+NOTICE:  10 15
+NOTICE:  10 15 20
+ execute_into_test 
+-------------------
+ (1,2)
+(1 row)
+
+drop table eifoo cascade;
+drop type eitype cascade;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 7ea7c8c6e0c..314f69915fc 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2018,3 +2018,35 @@ select missing_return_expr();
 
 drop function void_return_expr();
 drop function missing_return_expr();
+
+--
+-- EXECUTE ... INTO test
+--
+
+create table eifoo (i integer, y integer);
+create type eitype as (i integer, y integer);
+
+create or replace function execute_into_test(varchar) returns record as $$
+declare
+    _r record;
+    _rt eifoo%rowtype;
+    _v eitype;
+    i int;
+    j int;
+    k int;
+begin
+    execute 'insert into '||$1||' values(10,15)';
+    execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
+    raise notice '% %', _r.i, _r.y;
+    execute 'select * from '||$1||' limit 1' into _rt;
+    raise notice '% %', _rt.i, _rt.y;
+    execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
+    raise notice '% % %', i, j, k;
+    execute 'select 1,2' into _v;
+    return _v;
+end; $$ language plpgsql;
+
+select execute_into_test('eifoo');
+
+drop table eifoo cascade;
+drop type eitype cascade;