PLpgsql语言中returning-into的使用方法 总结

    公司的项目是在开源数据库PostgreSQL基础之上的，项目需求是要PostgreSQL数据库兼容oracle的函数。

在PostgreSQL数据库中写函数有很多方法，可以使用SQL语言，PL/pgSQL语言或者C语言都行。虽然PL/pgsql

相对于oracle中的PL/sql还有诸多不足之处，可是PL/pgsql发展很快，同样小巧好用。刚才得知postgres 9.5

刚刚发布，好像修复了不少bug。加油postgres。好了，进入正题。

    在写函数的过程中经常涉及到对表的增(insert)删(delete)改(update)查(select)操作，最常见的需求

就是在对表操作后，获取操作行的某些列的值，或者获取操作行所有的数据。最常使用的方法是使用如下方法：

• insert-select
• select-delete
• update-select
• select-into

    在看postgres的官方文档时，发现还有一种方法(returning-into方法)同样可以实现上面的前三个功能，而

select-into方式已经相当简洁了，就不用使用returning-into形式了。特把returning-into的使用方法总结

如下：

    

下面是测试表结构及数据：

create table table_name(id integer, name text, addr text); insert into table_name values(1,'张三','南京市，玄武区'); insert into table_name values(2,'李四','南京市，雨花区'); insert into table_name values(3,'王五','南京市，鼓楼区');

 一、增(insert)操作

    增(insert)操作的过程中可以获取操作行某些列的值，同样也可以获取操作行所有列的值。

    下面分两部分对增操作中使用returning-into进行介绍。

    1.在增(insert)操作的过程中使用returning-into实现获取部分列的值，当然你也可以只获取特定

    的某一列的值。

CREATEORREPLACEFUNCTION insert_get_column() RETURNS text AS $$ DECLARE     get_name text;     get_addr text; BEGIN     INSERTINTO table_name VALUES(4,'赵六','南京市，江宁区')     RETURNING name, addr INTO get_name, get_addr;         --使用insert-select形式实现相同功能     --INSERT INTO table_name VALUES (4, '赵六', '南京市，江宁区');     --select name, addr into get_name, get_addr from table_name where id = 4;       RETURN get_name ||':'|| get_addr; END; $$ LANGUAGE plpgsql;

    

   2.在增 (insert)操作的过程中使用returning-into实现获取所有列的值。

CREATEORREPLACEFUNCTION insert_get_row() RETURNS SETOF table_name AS $$ DECLARE     get_row table_name%ROWTYPE; BEGIN     INSERTINTO table_name VALUES(5,'david','南京市，江宁新区')     RETURNING *INTO get_row;         --上面一行语句与下面这行命令功能相同     --INSERT INTO table_name VALUES (5, 'david', '南京市，江宁新区')     --RETURNING id, name, addr INTO get_row.id, get_row.name, get_row.addr;         --使用insert-select形式实现相同功能     --INSERT INTO table_name VALUES (5, 'david', '南京市，江宁新区');     --select * into get_row from table_name where id = 5;       RETURNNEXT get_row;     RETURN; END; $$ LANGUAGE plpgsql;

二、删(delete)操作

    删(delete)操作的过程中可以获取操作行某些列的值，同样也可以获取操作行所有列的值。

    下面分两部分对删操作中使用returning-into进行介绍。

    1.在删(delete)操作的过程中使用returning-into实现获取部分列的值，当然你也可以只获取特定

    的某一列的值。

CREATEORREPLACEFUNCTION delete_get_column(num_id integer) RETURNS text AS $$ DECLARE     get_name text;     get_addr text; BEGIN     DELETEFROM table_name WHERE id = num_id     RETURNING name, addr INTO get_name, get_addr;         --使用select-delete形式实现相同功能     --SELECT name, addr INTO get_name, get_addr FROM table_name WHERE id = num_id;     --DELETE FROM table_name WHERE id = num_id;         RETURN get_name ||':'|| get_addr; END; $$ LANGUAGE plpgsql;

  2.在删(delete)操作的过程中使用returning-into实现获取所有列的值。

CREATEORREPLACEFUNCTION delete_get_row(num_id integer) RETURNS SETOF table_name AS $$ DECLARE     get_row table_name%ROWTYPE; BEGIN     DELETEFROM table_name WHERE id = num_id     RETURNING *INTO get_row;           --上面一行语句与下面这行命令功能相同     --DELETE FROM table_name WHERE id = num_id     --RETURNING id, name, addr INTO get_row.id, get_row.name, get_row.addr;         --使用select-delete形式实现相同功能     --select * into get_row from table_name where id = num_id;     --DELETE FROM table_name WHERE id = num_id;         RETURNNEXT get_row;     RETURN; END; $$ LANGUAGE plpgsql;

三、改(update)操作

    改(update)操作的过程中可以获取操作行某些列的值，同样也可以获取操作行所有列的值。

    下面分两部分对删操作中使用returning-into进行介绍。

    1.在改(update)操作的过程中使用returning-into实现获取部分列的值，当然你也可以只获取特定

    的某一列的值。

CREATEORREPLACEFUNCTION update_get_column(num_id integer) RETURNS text AS $$ DECLARE     get_name text;     get_addr text; BEGIN     UPDATE table_name SET addr ='new_address'WHERE id = num_id     RETURNING name, addr into get_name, get_addr;           --使用update-select形式实现相同功能     --UPDATE table_name SET addr = 'new_address' WHERE id = num_id ;     --select name, addr into get_name, get_addr from table_name where id = num_id;         RETURN get_name ||':'|| get_addr; END; $$ LANGUAGE plpgsql;

   2.在改(update)操作的过程中使用returning-into实现获取所有列的值。

CREATEORREPLACEFUNCTION update_get_row(num_id integer) RETURNS SETOF table_name AS $$ DECLARE     get_row table_name%ROWTYPE; BEGIN     UPDATE table_name SET addr ='new_address'WHERE id = num_id     RETURNING *into get_row;       --上面一行语句与下面一行命令功能相同     --UPDATE table_name SET addr = 'new_address' WHERE id = num_id     --RETURNING id, name, addr into get_row.id, get_row.name, get_row.addr;         --使用update-select形式实现相同功能     UPDATE table_name SET  addr ='new_address'WHERE id = num_id;     select*into get_row from table_name where id = num_id;       RETURNnext get_row;     RETURN; END; $$ LANGUAGE plpgsql;

四、查(select)操作

    查(select)操作不支持returning-into操作。使用select-into形式就能实现获取操作行某些列的值，

    同样也可以获取操作行所有列的值。

    1.使用select-into形式实现获取某些列的值。

CREATEORREPLACEFUNCTION select__get_column(num_id integer) RETURNS text AS $$ DECLARE     get_name text;     get_addr text; BEGIN     SELECT name, addr INTO get_name, get_addr FROM table_name WHERE id = num_id;     IFNOTFOUNDTHEN         RAISE NOTICE 'NOT FOUND %',num_id;     ENDIF;       RETURN get_name ||':'|| get_addr; END; $$ LANGUAGE plpgsql;

    2.使用select-into形式实现 获取所有列的值。

CREATEORREPLACEFUNCTION select_get_row(num_id integer) RETURNS SETOF table_name AS $$ DECLARE     get_row table_name%ROWTYPE; BEGIN     SELECT*INTO get_row FROM table_name WHERE id = num_id;     IFNOTFOUNDTHEN         RAISE NOTICE 'NOT FOUND %',num_id;     ENDIF;       RETURNNEXT get_row;     RETURN; END; $$ LANGUAGE plpgsql;

@end