PostgreSQL function返回多行的操作

1. 建表

postgres=# create table tb1(id integer,name character varying);
CREATE TABLE
postgres=#
postgres=# insert into tb1 select generate_series(1,5),'aa';
INSERT 0 5 

2. 返回单字段的多行(returns setof datatype)

不指定out参数,使用return next xx:

create or replace function func01()returns setof character varying as $$
declare
n character varying;
begin
 for i in 1..5 loop
 select name into n from tb1 where id=i;
 return next n;
 end loop;
end
$$ language plpgsql;

指定out参数,使用return next:

create or replace function func02(out character varying)returns setof character varying as $$
begin
 for i in 1..5 loop
 select name into $1from tb1 where id=i;
 return next;
 end loop;
end
$$ language plpgsql;

使用return query:

create or replace function func03()returns setof character varying as $$
begin
 for i in 1..5 loop
 return query(select name from tb1 where id=i);
 end loop;
end
$$language plpgsql;

3. 返回多列的多行(returns setog record)

不指定out参数,使用return next xx:

create or replace function func04()RETURNS SETOF RECORD as $$
declare
 r record;
begin
 for i in 1..5 loop
 select * into r from tb1 where id=i;
 return next r;
 end loop;
end;
$$language plpgsql;

在使用func04的时候注意,碰到问题列下:

问题一:

postgres=# select func04();
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function func04() line 7 at RETURN NEXT

解决:

If you call your set-returning function the wrong way (IOW the way you might normally call a function), you will get this error message: Set-valued function called in context that cannot accept a set. Incorrect: select sr_func(arg1, arg2, …); Correct: select * from sr_func(arg1, arg2, …);

问题二:

postgres=# select * from func04();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from func04();

解决:

postgres=# select * from func04() as t(id integer,name character varying);
 id | name
----+------
 1 | aa
 2 | aa
 3 | aa
 4 | aa
 5 | aa
(5 rows)

这个问题在func04如果指定out参数就不会有问题,如下func05所示:

指定out参数,使用return next:

create or replace function func05(out out_id integer,out out_name character varying)returns setof record as $$
declare
 r record;
begin
 for i in 1..5 loop
 select * into r from tb1 where id=i;
 out_id:=r.id;
 out_name:=r.name;
 return next;
 end loop;
end;
$$language plpgsql;
postgres=# select * from func05();
 id | name
----+------
 1 | aa
 2 | aa
 3 | aa
 4 | aa
 5 | aa
(5 rows)

使用return query:

create or replace function func06()returns setof record as $$
begin
 for i in 1..5 loop
 return query(select id,name from tb1 where id=i);
 end loop;
end;
$$language plpgsql;
postgres=# select * from func06() as t(id integer,name character varying);
 id | name
----+------
 1 | aa
 2 | aa
 3 | aa
 4 | aa
 5 | aa
(5 rows)

补充:Postgresql - plpgsql - 从Function中查询并返回多行结果

通过plpgsql查询表,并返回多行的结果。

关于创建实验表插入数据这里就不说啦

返回查询结果

mytest=# create or replace function test_0830_5() returns setof test
mytest-# as $$
mytest$# DECLARE
mytest$# r test%rowtype; -- 将
mytest$# BEGIN
mytest$# FOR r IN
mytest$# SELECT * FROM test WHERE id > 0
mytest$# LOOP
mytest$# RETURN NEXT r;
mytest$# END LOOP;
mytest$# RETURN;
mytest$# END
mytest$# $$ language plpgsql;
CREATE FUNCTION

mytest=# select test_0830_5(1);
test_0830_5
------------------------------------------
(2,abcabc,"2018-08-30 09:26:14.392187")
......
(11,abcabc,"2018-08-30 09:26:14.392187")
(10 rows)

mytest=# select * from test_0830_5();
id | col1 | col2
----+--------+----------------------------
2 | abcabc | 2018-08-30 09:26:14.392187
......
11 | abcabc | 2018-08-30 09:26:14.392187
(10 rows)

返回某列

mytest=# CREATE OR REPLACE FUNCTION test_0830_6(date) RETURNS SETOF integer AS $$
mytest$# BEGIN
mytest$# RETURN QUERY SELECT id
mytest$# FROM test
mytest$# WHERE col2 >= $1
mytest$# AND col2 < ($1 + 1);
mytest$# IF NOT FOUND THEN
mytest$# RAISE EXCEPTION 'No id at %.', $1;
mytest$# END IF;
mytest$# RETURN;
mytest$# END
mytest$# $$
mytest-# LANGUAGE plpgsql;
CREATE FUNCTION
mytest=# select test_0830_6('2018-08-30');
test_0830_6
-------------
2
......
11
(10 rows)

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • postgreSQL数据库 实现向表中快速插入1000000条数据

    不用创建函数,直接向表中快速插入1000000条数据 create table tbl_test (id int, info text, c_time timestamp); insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp(); select count(id) from tbl_test; --查看个数据条数 补充:postgreSQL 批量插入10000条数据

  • postgresql插入后返回id的操作

    如下所示: 补充:PostgreSQL中执行insert同时返回插入的那行数据 通过使用语句: INSERT INTO tab1 ... RETURNING *; 以上这篇postgresql插入后返回id的操作就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.

  • Postgresql锁机制详解(表锁和行锁)

    表锁 LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ] lockmode包括以下几种: ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE LOCK TABLE命令用于获取一个表锁,获取过程将阻塞一直

  • Python操作PostgreSql数据库的方法(基本的增删改查)

    Python操作PostgreSql数据库(基本的增删改查) 操作数据库最快的方式当然是直接用使用SQL语言直接对数据库进行操作,但是偶尔我们也会碰到在代码中操作数据库的情况,我们可能用ORM类的库对数控库进行操作,但是当需要操作大量的数据时,ORM的数据显的太慢了.在python中,遇到这样的情况,我推荐使用psycopg2操作postgresql数据库 psycopg2 官方文档传送门: http://initd.org/psycopg/docs/index.html 简单的增删改查 连接

  • postgreSql分组统计数据的实现代码

    1. 背景 比如气象台的气温监控,每半小时上报一条数据,有很多个地方的气温监控,这样数据表里就会有很多地方的不同时间的气温数据 2. 需求: 每次查询只查最新的气温数据按照不同的温度区间来分组查出,比如:高温有多少地方,正常有多少地方,低温有多少地方 3. 构建数据 3.1 创建表结构: -- DROP TABLE public.t_temperature CREATE TABLE public.t_temperature ( id int4 NOT NULL GENERATED ALWAYS

  • postgresql 实现数据的导入导出

    最近想把服务器上的测试数据库数据导到我本地的电脑上,本地电脑数据库是安装在windows系统下 之前没使用过pgsql,网上找了点资料,记入如下: 一,首先把服务器上的数据进行备份 pg_dump -U 用户名 数据库名 (-t 表名)> 数据存放路径 二,把.sql 文件下载到本地之后,首先切换到pgsql路径下的bin目录 然后执行这条命令: -d:数据库名 -h:地址 -p:端口 -u:用户名 -f:sql文件路径 之后输入口令: 这样就可以了! 补充:Sqoop从PostgreSQL导入

  • 查看postgresql数据库用户系统权限、对象权限的方法

    PostgreSQL简介 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统.POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中.PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询.外键.触发器.视图.事务完整性.多版本并发控制等.同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型.函数.操作符

  • 基于postgresql行级锁for update测试

    创建表: CREATE TABLE db_user ( id character varying(50) NOT NULL, age integer, name character varying(100), roleid character varying, CONSTRAINT db_user_pkey PRIMARY KEY (id) ) 随便插入几条数据即可. 一.不加锁演示 1.打开一个postgreSQL的SQL Shell或pgAdmin的SQL编辑器窗口,执行: begin; s

  • PostgreSQL function返回多行的操作

    1. 建表 postgres=# create table tb1(id integer,name character varying); CREATE TABLE postgres=# postgres=# insert into tb1 select generate_series(1,5),'aa'; INSERT 0 5 2. 返回单字段的多行(returns setof datatype) 不指定out参数,使用return next xx: create or replace fun

  • PostgreSQL 定义返回表函数的操作

    本文我们学习如何在PostgreSQL 开发返回表函数. 示例数据表 我们使用的示例数据库表为film,如下图所示: 示例1 第一个函数发挥所有满足条件film表记录,这里使用ilike操作,和like类似,但不区分大小写: CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR) RETURNS TABLE ( film_title VARCHAR, film_release_year INT ) AS $$ BEGIN RETURN QU

  • PostgreSQL 实现子查询返回多行的案例

    作为一个表达式使用的子查询返回了多列: 在查询中,我们需要以第2条查询语句作为第一条查询语句的条件,但是第一条根据这个条件查询出来是多个数据,这时候又需要保留多个数据,运用子查询就会报错, 以下就为解决这种多对多关系查询,且没有关联关系的表的解决方案: select c.rain_value,c.ad_nm from ( select *, json::json->t2.lon_lat as rain_value from actual_time_model_json t1, (SELECT D

  • PostgreSQL 实现将多行合并转为列

    需求将下列表格相同id的name拼接起来输出成一列 id Name 1 peter 1 lily 2 john 转化后效果: id Name 1 peter:lily 2 john: 实现方式使用 array_to_string 和 array_agg 函数,具体语句如下: string_agg(expression, delimiter) 把表达式变成一个数组 string_agg(expression, delimiter) 直接把一个表达式变成字符串 select id, array_to

  • C#/.NET使用git命令行来操作git仓库的方法示例

    我们可以在命令行中操作 git,但是作为一名程序员,如果在大量重复的时候还手动敲命令行,那就太笨了. 本文介绍使用 C# 编写一个 .NET 程序来自动化地使用 git 命令行来操作 git 仓库. 这是一篇很基础的入门文章. 最简单的运行 git 命令的代码 在 .NET 中,运行一个命令只需要使用 Process.Start 开启一个子进程就好了.于是要运行一个 git 命令,我们其实只需要这句足以: Process.Start("git", "status")

  • 对python for 文件指定行读写操作详解

    1.os.mknod("test.txt") #创建空文件 2.fp = open("test.txt",w) #直接打开一个文件,如果文件不存在则创建文件 3.关于open 模式: 详情: w:以写方式打开, a:以追加模式打开 (从 EOF 开始, 必要时创建新文件) r+:以读写模式打开 w+:以读写模式打开 (参见 w ) a+:以读写模式打开 (参见 a ) rb:以二进制读模式打开 wb:以二进制写模式打开 (参见 w ) ab:以二进制追加模式打开 (

  • python 读txt文件,按‘,’分割每行数据操作

    按行读取TXT文件 fname = './新建文件夹/yob2010.txt' //文件夹路径 with open(fname,'r+',encoding='utf-8') as f: for line in f.readlines(): //按行读取每行 print(line[:-1].split(',')) //切片去掉换行符,再以','分割字符串 ,得到一个列表 s = [i[:-1].split(',') for i in f.readlines()] //列表生成器,将文件每行数据按上

  • VUE UPLOAD 通过ACTION返回上传结果操作

    通过Upload 的action方法 返回不了结果,可以通过on-success方法中获取返回结果 <Upload accept=".xls, .xlsx" :action="uploadUrl" :on-success="onSuccess" :on-error="handleError" :before-upload="beforeUpload" style="float:right&q

  • numpy 实现返回指定行的指定元素的位置索引

    先上代码,主要语句为np.where(b[c]==1), 详细解释如下: import numpy as np b = np.array([[-2,-3,0,0,0,6,4,1],[88,1,0,0,0,6,4,2],[99,6,0,0,1,6,4,2]]) # 三行八列的数组b print('b\n',b) c = np.array([2,0]) # c表示指定行 print('b[c]\n',b[c]) # b[c]返回 数组b的指定行 这里依次返回了b的下标为2和0的行 print('\n

  • pandas重复行删除操作df.drop_duplicates和df.duplicated的区别

    目录 概念 df.duplicated() df.dropduplicates(’col‘,keep=‘first’,inplace=False) 实例 概念 df.duplicated() 使用df.cuplicated()来查看重复数据,返回True,False,数据类型是bool. 也可以指定某一列是否有重复值df.cuplidated(‘colname’),不指定则默认为第一列. df.dropduplicates(’col‘,keep=‘first’,inplace=False) 用来

随机推荐