Postgresql自定义函数详解

PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。

语法：

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
 DECLARE
  declaration;
  [...]
 BEGIN
  < function_body >
  [...]
  RETURN { variable_name | value }
 END; LANGUAGE plpgsql;

参数说明

function_name:指定函数的名称。

[OR REPLACE]:是可选的，它允许修改/替换现有函数。

DECLARE:定义参数(参数名写在前面 类型写在后面)。

BEGIN~END: 在中间写方法主体。

RETURN:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。

LANGUAGE:它指定实现该函数的语言的名称。

下面我将创建一个简单的函数：

CREATE OR REPLACE FUNCTION test(id uuid)
 RETURNS INTEGER
 LANGUAGE plpgsql
AS
$$
declare
 count integer;
begin
 count = (SELECT
       json_array_length(A.json::json -> 'features')
      FROM "Json" A
      WHERE A.uid = id);
 return count;
end;

$$

该函数的功能是用来返回json中数组的长度的

定义好该函数后，我们可以像调用其他的函数一样调用它

SELECT test(id)

返回数组长度(integer)

补充：PostgreSQL中自定义函数（function）返回数据集

1.准备数据

建表

 create table city (
 cityId int,
 cityName varchar(20)
 );

插入数据

insert into city values(1,'BeiJing'),(2,'NewYork'),(3,'Hong kong'),(4,'ShaingHai');

2. 函数实例

2.1 setof 表名

创建函数getCity()，它返回city表中所有的数据，返回值声明为 setof 表名。

create or replace function getCity() returns setof city as
$$
begin
return query select * from city;
end;
$$
language plpgsql;

执行函数。

mydb=# select getCity();
   getcity
-----------------
 (1,BeiJing)
 (2,NewYork)
 (3,"Hong kong")
 (4,ShaingHai)
(4 行记录)

也可以用类似查询表的方式。

mydb=# select * from getCity();
 cityid | cityname
--------+-----------
   1 | BeiJing
   2 | NewYork
   3 | Hong kong
   4 | ShaingHai
(4 行记录)

PostgreSQL还支持对函数执行结果进行条件判断并过滤。

mydb=# select * from getCity() where cityId > 3;
 cityid | cityname
--------+-----------
   4 | ShaingHai
(1 行记录)

2.2 setof record

为了使函数更加通用，以解决动态返回数据集的问题。创建函数getRows(text)，将表名city作为函数参数，它返回表中所有的数据，返回值声明为 setof record。

create or replace function getRows(text) returns setof record as
$$
declare
rec record;
begin
for rec in EXECUTE 'select * from ' || $1 loop
return next rec;
end loop;
return;
end
$$
language 'plpgsql';

执行函数。

mydb=# select * from getRows('city') as city(id int, name varchar(20));
 id |  name
----+-----------
 1 | BeiJing
 2 | NewYork
 3 | Hong kong
 4 | ShaingHai
(4 行记录)

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

赞 (0)

相关推荐

  • Postgresql 通过出生日期获取年龄的操作

    Postgresql 通过出生日期获取年龄的操作

    三个基础的时间表示函数 CURRENT_DATE/CURRENT_TIME/NOW() SELECT CURRENT_DATE ; 返回当前日期以 年-月-日(yyyy-MM-dd)的形式: 2019-01-10 SELECT CURRENT_TIME; 返回当日时间以 时:分:秒+时区(HH:mm:ss )的形式: 17:49:11.585308+08 SELECT NOW(); 返回当前时间 以 年-月-日 时:分:秒(yyyy-MM-dd HH:mm:ss)的形式: 2019-01-10

  • Postgresql排序与limit组合场景性能极限优化详解

    Postgresql排序与limit组合场景性能极限优化详解

    1 构造测试数据 create table tbl(id int, num int, arr int[]); create index idx_tbl_arr on tbl using gin (arr); create or replace function gen_rand_arr() returns int[] as $$ select array(select (1000*random())::int from generate_series(1,64)); $$ language sq

  • postgresql的now()与Oracle的sysdate区别说明

    postgresql的now()为当前事务开始时间, 而Oracle的sysdate是当前时间. 区别在于事务. postgresql中的now(): postgres=# begin ; BEGIN postgres=# select now(); now ------------------------------- 2017-03-31 14:28:32.403869+08 (1 row) postgres=# select now(); now ---------------------

  • postgresql 实现sql多行语句合并一行

    多行语句合并一行 三个表关联查询的结果集为: SELECT users.name,users.age,users.birthday,roles.name FROM users,users_roles,roles WHERE users_roles.userid = users.id and users_roles.roleid = roles.id name age birthday role 张三 23 1993-1-1 role1 张三 23 1993-1-1 role2 张三 23 199

  • PostgreSQL删除更新优化操作

    1. 先说删除吧,因为刚搞了. 删除缓慢的原因:主要是约束的问题.(数据库在有约束的时候,进行操作,会根据约束对相关表进行验证,可想而知,20W的数据验证要耗费多久的时间).其次就是sql的编写.(sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>).索引的问题 请看下面的 补充部分 具体解决方法: ALTER TABLE tableName DISABLE TRIGGER ALL; delete 目标语句 ALTER TABLE tableName E

  • 详解PostgreSql数据库对象信息及应用

    PostgreSql数据库对象主要有数据库.表.视图.索引.schema.函数.触发器等.PostgreSql提供了information_schema schema,其中包括返回数据库对象的视图.如用户有访问权限,可以也在pg_catalog schema中查询表.视图等对象. 1. 查询数据库对象 下面通过示例分别展示如何查询各种数据库对象. 1.1 表查询 PostgreSql 表信息可以从information_schema.tables 或 pg_catalog.pg_tables 视

  • postgreSQL 非count方法算记录数操作

    一般方法 select count(1) from table_name; 全量扫描一遍表,记录越多,查询速度越慢 新法 PostgreSQL 还真提供了一个这样的途径,那就是系统表 pg_class,这个系统表里头,存储着每个表的统计信息,其中 reltuples 就是对应的表的统计行,统计行的数据是pg有个独立进程,定期扫描不同的表,收集这些表的统计信息,保存在系统表里头. 方法如下: select reltuples::int as total from pg_class where re

  • postgresql通过索引优化查询速度操作

    postgresql通过索引优化查询速度操作

    当数据量比较大的时候,提升查询效率就是需要去考虑的事情了.一个百万级别的表格,如果不做任何优化的话,即使是最简单的查询语句执行起来也是慢的让人难以接受:当然"优化"本身是一个比较复杂的工程,从设计表.字段到查询语句的写法都有很多讲究,这里只考虑索引的方式,且是最普通的索引: 下面的操作中对应数据库表w008_execrise_info(8000数据量), w008_wf02_info(4000数据量) 1 任务表数据 SELECT w.* FROM w008_wf02_info w W

  • Postgresql自定义函数详解

    PostgreSQL函数也称为PostgreSQL存储过程. PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明,分配,循环,控制流程等). 语法: CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; [...] BEGIN < function_body

  • SQL Function 自定义函数详解

    SQL Function 自定义函数详解

    目录 产生背景(已经有了存储过程,为什么还要使用自定义函数) 发展历史 构成 使用方法 适用范围 注意事项 疑问 内容 产生背景(已经有了存储过程,为什么还要使用自定义函数) 与存储过程的区别(存在的意义): 1.     能够在select等SQL语句中直接使用自定义函数,存储过程不行. 2.     自定义函数可以调用其他函数,也可以调用自己(递归) 3.     可以在表列和 CHECK 约束中使用自定义函数来实现特殊列或约束 4.       自定义函数不能有任何副作用.函数副作用是指对

  • Oracle存储过程和自定义函数详解

    Oracle存储过程和自定义函数详解

    概述 PL/SQL中的过程和函数(通常称为子程序)是PL/SQL块的一种特殊的类型,这种类型的子程序可以以编译的形式存放在数据库中,并为后续的程序块调用. 相同点: 完成特定功能的程序 不同点:是否用return语句返回值. 举个例子: create or replace procedure PrintStudents(p_staffName in xgj_test.username%type) as cursor c_testData is select t.sal, t.comm from

  • Golang中的自定义函数详解

    Golang中的自定义函数详解

    不管是面向过程的编程,还是面向对象的编程,都离不开函数的概念,分别是,参数,函数名,返回值.接下来我们看看Go语言在这三个方面是做怎么操作的吧. 参数 谈到参数,我们在写函数或者是类中的方法的时候都需要考虑我们应该传递怎样的参数,或者是是否需要参数. 参数首先分为无参函数有参.无参也就是没有参数,也就不用写了. 有参 func functionTest() {  # 小括号内就是用来放参数的     # 函数体内 } Go语言是强数据类型的语言,参数是要指定类型的不然就报错.func 是函数的声

  • vue自定义全局共用函数详解

    如果你需要让一个工具函数在每个组件可用,可以把方法挂载到 Vue.prototype上. 在main.js中: Vue.prototype.method = function () {} 组件中调用: this.method() 以上这篇vue自定义全局共用函数详解就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.

  • PostgreSQL中的日期/时间函数详解

    PostgreSQL中的日期/时间函数详解

    目录 零.前言 一.获取当前时间 二.时间的加减 三.格式化函数 3.1时间转字符串 3.2字符串转日期 3.3字符串转时间 3.4Unix时间戳转时间 四.一些重要函数 4.1时间间隔 4.2时间截取 五.时间的转换 六.收! 零.前言 公司里有一台阿里云RDS数据库用了PPAS(Postgres PlusTM Advanced Server),在处理日期/时间时遇到一些问题,花了点时间整理如下. 一.获取当前时间 select now() select current_timestamp s

  • Vue自定义事件(详解)

    前面的话 父组件使用props传递数据给子组件,子组件怎么跟父组件通信呢?这时,Vue的自定义事件就派上用场了.本文将详细介绍Vue自定义事件 事件绑定 每个 Vue 实例都实现了事件接口 (Events interface),即 使用 $on(eventName) 监听事件 使用 $emit(eventName) 触发事件 [注意]Vue 的事件系统分离自浏览器的EventTarget API.尽管它们的运行类似,但是 $on 和 $emit 不是addEventListener 和 disp

  • Vue渲染函数详解

    Vue渲染函数详解

    前面的话 Vue 推荐在绝大多数情况下使用 template 来创建HTML.然而在一些场景中,真的需要 JavaScript 的完全编程的能力,这就是 render 函数,它比 template 更接近编译器.本文将详细介绍Vue渲染函数 引入 下面是一个例子,如果要实现类似下面的效果.其中,H标签可替换 <h1> <a name="hello-world" href="#hello-world" rel="external nofol

  • python魔法方法-自定义序列详解

    python魔法方法-自定义序列详解

    自定义序列的相关魔法方法允许我们自己创建的类拥有序列的特性,让其使用起来就像 python 的内置序列(dict,tuple,list,string等). 如果要实现这个功能,就要遵循 python 的相关的协议.所谓的协议就是一些约定内容.例如,如果要将一个类要实现迭代,就必须实现两个魔法方法:__iter__.next(python3.x中为__new__).__iter__应该返回一个对象,这个对象必须实现 next 方法,通常返回的是 self 本身.而 next 方法必须在每次调用的时

  • ThinkPHP函数详解之M方法和R方法

    首先给大家介绍ThinkPHP函数详解:M方法 M方法用于实例化一个基础模型类,和D方法的区别在于: 1.不需要自定义模型类,减少IO加载,性能较好: 2.实例化后只能调用基础模型类(默认是Model类)中的方法: 3.可以在实例化的时候指定表前缀.数据库和数据库的连接信息: D方法的强大则体现在你封装的自定义模型类有多强,不过随着新版ThinkPHP框架的基础模型类的功能越来越强大,M方法也比D方法越来越实用了. M方法的调用格式: M('[基础模型名:]模型名','数据表前缀','数据库连接

随机推荐