postgresql学习-自定义函数

最新推荐文章于 2024-06-18 19:13:03 发布
最新推荐文章于 2024-06-18 19:13:03 发布
阅读量1k 收藏
点赞数
分类专栏： PG
原文链接：https://blog.csdn.net/u011165335/article/details/99895650
版权

————————————————
版权声明：本文为CSDN博主「尚云峰」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u011165335/article/details/99895650

在pg里面,只有function
1.删除函数 函数名+参数签名
drop FUNCTION if exists HelloWorld2(varchar);

2.CREATE OR REPLACE FUNCTION 不允许你修改一个现有函数的返回类型。
– 要做这些事情，你必须删除并重新创建函数。
–LANGUAGE plpgsql yelowbick只有过程,跟pgsql刚好相反

–入参:anyelement 基本类型 表类型 游标
–返回:基本类型,记录类型,游标,json,table类型,数组

 --块language plpgsql可以注释掉   方便用来测试
do --language plpgsql
    $$
        begin
           raise notice 'ok';
        end;
    $$;

3.行对象row
select * from dept;
SELECT ROW(‘10’, ‘aaa’, ‘hhhh’)::dept;
– 记录变量record类似于行类型变量，但是它们没有预定义的结构，
– 只能通过SELECT或FOR命令来获取实际的行结构，因此记录变量在被初始化之前无法访问，否则将引发运行时错误。
–注：RECORD不是真正的数据类型，只是一个占位符。
 

4.LANGUAGE SQL

-- LANGUAGE SQL 简单的DML语句
create or replace  FUNCTION func_get() RETURNS int AS
$$
select 1;
$$ LANGUAGE SQL;

select * from func_get();


create or replace function add_two(int,int)
returns int
as
$$
    select $1+$2;
$$ LANGUAGE sql;
select * from add_two(1,2);

--returning返回更新前的值
drop function update_emp(varchar);
create or replace function update_emp(varchar)
returns varchar
as
$$
    update emp set ename=$1||'ysy' where ename=$1
    returning ename;
$$ LANGUAGE sql;
select * from update_emp('SMITH');


--复合类型
create or replace function sel_emp(emp)
returns varchar
as
$$
   select $1.ename;
$$ LANGUAGE sql;
 select sel_emp(e.*) from emp e;


--输出类型  这里等价于add_two
CREATE FUNCTION add_two2 (IN x int, IN y int, OUT sum int) AS $$
        SELECT $1 + $2*2;
$$ LANGUAGE SQL;

CREATE FUNCTION add_two3 (IN x int, IN y int, OUT sum1 int,out sum2 int) AS
$$
        SELECT $1 + $2*2,$1 + $2;
$$ LANGUAGE SQL;
select * from add_two3(1,2);


--返回集合-----------------------------------------
-- 类型为emp,默认之返回第一条记录
CREATE FUNCTION getfoo1() RETURNS emp AS $$
        SELECT * FROM emp ;
    $$ LANGUAGE SQL;
select * from getfoo1();

--如果要全部返回setof table
CREATE FUNCTION getfoo2() RETURNS  setof emp AS $$
        SELECT * FROM emp ;
    $$ LANGUAGE SQL;
select * from getfoo2();
--可以访问指定列
select  (getfoo2()).ename;


drop function getfoo2_1;
--可以指定返回表的具体字段类型
create or replace function getfoo2_1() RETURNS   emp.ename%type  AS $$
        SELECT ename FROM emp ;
    $$ LANGUAGE SQL;
select * from getfoo2_1();


--可以返回记录类型
create or replace function  getfoo3() RETURNS  record AS $$
        SELECT empno,ename FROM emp ;
    $$ LANGUAGE SQL;
select  getfoo3() ;
--如何访问记录类型结果集呢   类型要一致，这个声明确实有点不方便
select t.ename from  getfoo3() t (empno integer,ename varchar);

--自定义返回类型
create  type rec_type as (empno int,ename varchar);
create or replace function  getfoo4() RETURNS  rec_type AS $$
        SELECT empno,ename FROM emp ;
    $$ LANGUAGE SQL;
--这里type里面指定了,不需要声明
select t.ename from  getfoo4() t ;


--多态
 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
        SELECT ARRAY[$1, $2];
    $$ LANGUAGE SQL;
select * from make_array(1,1);
--字符要指定类型
select * from make_array('a'::text,'n'::text);

--  如果PL/pgSQL函数的返回类型为多态类型(anyelement或anyarray)，那么函数就会创建一个特殊的参数：$0。
--  我们仍然可以为该变量设置别名。
    create or replace function  add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
    RETURNS anyelement AS $$
    DECLARE
        result ALIAS FOR $0;
    BEGIN
        result := v1 + v2 + v3;
        RETURN result;
        --RETURN (v1 + v2 + v3); --等价
    END;
    $$ LANGUAGE plpgsql;
select * from add_three_values(1,2,3);

--重载
  CREATE FUNCTION test(int, real) RETURNS ...
  CREATE FUNCTION test(int, varchar) RETURNS ...

5.language plpgsql

返回集合类型和json的方式如下:

 

-- language plpgsql
--返回record
create or replace function getRecord()
    returns record
as
$$
declare
    v_arr record;
begin
    v_arr := ('aaa'::varchar,'bbb'::varchar);
    return v_arr;
end ;
$$ language plpgsql;

select t.* from getRecord() t (a varchar,b varchar);

--返回数组
create or replace function getArr()
    returns int[]
as
$$
declare
    v_arr int[];
begin
    v_arr := array [1,2];
    return v_arr;
end ;
$$ language plpgsql;


--返回json
create or replace function getJson()
    returns json
as
$$
declare
    v_arr json;
begin
    v_arr := '{"name":"李易峰","sex":"男"}';
    return v_arr;
end ;
$$ language plpgsql;

--测试
do
$$
declare
    v_rec1 varchar;
    v_rec2 varchar;
    v_arr int[];
    v_json json;
begin
    --record
    select t.* into v_rec1,v_rec2 from getRecord() t (a varchar,b varchar);
    raise notice 'record=,%,%',v_rec1,v_rec2;
    --arr
    v_arr:=getArr();
    raise notice 'arr=,%',v_arr[1];
    v_json:=getJson();
    raise notice 'json=,%',v_json->>'name';
end;
$$;

6.游标操作

--游标操作

--返回所有结果集
create or replace function  getRcord() RETURNS  setof record AS $$
    declare
       rec record;
        --oracle cursor c_emp is ...
       c_emp cursor  for select ename from emp;
      --pg 不存在c_emp%rowType;这种类型,用record来接受
    begin
        for rec in c_emp  loop
        --将当前行的结果集插入rec
          return next rec;
        end loop;
        return ;
    end;
    $$ language plpgsql;
--需要声明
select * from  getRcord() t (ename varchar);


create or replace function  getRcord1_2(refcursor) RETURNS  setof refcursor AS $$
    declare
       rec record;
       ref alias for $1;
    begin
        open ref for select * from emp;
        return next ref;
    end;
    $$ language plpgsql;
select getRcord1_2('a'::refcursor);





--返回text 方式1
create or replace function  getRcord2() RETURNS  text  AS $$
    declare
       rec record;
       v_text text;
       c_emp cursor  for select * from emp;
    begin
        v_text:='';
       open c_emp;
       loop
           fetch  c_emp into rec;
           exit when not FOUND;
           v_text:=v_text||','||rec.ename;
       end loop;
       close c_emp;
    return v_text;
    end;
    $$ language plpgsql;
select  getRcord2();

--返回text 方式2
drop function getref;
create or replace function  getRcord3() RETURNS  text AS $$
    declare
     ref refcursor;
     v_rec record;
     v_text text:='';
    begin
        --open ref for  SELECT empno,ename FROM emp ;
        open ref for execute 'SELECT empno,ename FROM emp' ;
        --动态游标,只能loop循环,这点跟oracle是一样的
        loop
           fetch  ref into v_rec;
           exit when not found;
           v_text:=v_text||','||v_rec.ename;
        end loop;
        close ref;
        return v_text;
    exception when others
        then
        raise exception 'error,%',SQLERRM;
    end;
    $$ language plpgsql;
select * from getRcord3();

--动态游标
/*PL/pgSQL 函数可以向调用者返回游标。 这个功能用于从函数里返回多行或多列。要想这么做的时候， 该函数打开游标并且把该游标的名字返回给调用者。 调用者然后从游标里FETCH行。 游标可以由调用者关闭，或者是在事务结束的时候自动关闭。
函数返回的游标名可以由调用者声明或者自动生成。 要声明一个信使的名字，只要再打开游标之前，给 refcursor 变量赋予一个字串就可以了。 refcursor 变量的字串值将被 OPEN 当作下层的信使的名字使用。 不过，如果 refcursor 变量是空，那么 OPEN 将自动生成一个和现有信使不冲突的名字， 然后将它赋予 refcursor 变量。
注意: 一个绑定的游标变量其名字初始化为对应的字串值，因此信使的名字和游标变量名同名， 除非程序员再打开游标之前通过赋值覆盖了这个名字。但是一个未绑定的游标变量初始化的时候缺省是空， 因此它会收到一个自动生成的唯一的名字，除非被覆盖。
下面的例子显示了一个调用者声明游标名字的方法：*/
create or replace   FUNCTION ger_ref(refcursor) RETURNS refcursor AS
$$
BEGIN
       OPEN $1 FOR SELECT ename FROM emp;
       RETURN $1;
END;
$$ LANGUAGE plpgsql;

--只能用一次那个游标名,用
BEGIN;
SELECT ger_ref('funccursor');
FETCH ALL IN funccursor;
COMMIT;

--下面的例子使用了自动生成的游标名：
drop FUNCTION if exists ger_ref2;
 create or replace FUNCTION ger_ref2() RETURNS refcursor AS
 $$
 DECLARE
     ref refcursor;
 BEGIN
     OPEN ref FOR SELECT ename FROM emp;
     RETURN ref;
 END;
 $$ LANGUAGE plpgsql;

BEGIN;
SELECT ger_ref2();
FETCH ALL IN "<unnamed portal 1>";
COMMIT;

7.函数的调用

--函数的调用
-- yellow brick 是call和execute
-- select into 对于返回结果多行的,指挥取一行,如果结果为空,不会报错,跟oracle不一样
drop function into_test;
create or replace function into_test()
returns text as
    $$
     declare
         v_name emp.ename%type;
     begin
         select e.ename into v_name from emp e;
         if found then
             raise notice 'ename=%',v_name;
         end if;
         return 'ok';
     end;

    $$
    language plpgsql;

select into_test();

do $$
    declare
    v_text text;
begin
    --方式0
    --v_text:=into_test();
    --方式1
	--execute 'into_test()';
    --方式2 忽略返回值
    --perform into_test();
    -- 错误select into_test();
end
$$;

8.pgsql的批量操作

https://yq.aliyun.com/articles/54786?spm=a2c4e.11153940.0.0.28e2231f3pNJsx

https://yq.aliyun.com/articles/74420?do=login&accounttraceid=b0fcec2d-bb75-4f55-89a2-81fbcfb21110&do=login
————————————————
版权声明：本文为CSDN博主「尚云峰」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u011165335/article/details/99895650

sunnyxidian
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
postgresql-functions:自定义 PostgreSQL 函数和扩展
05-30
PostgreSQL 函数 自定义 PostgreSQL 函数和扩展的存储库。 diff_tables 一个简单的 plpgsql 函数，它接受两个表名（通过regclass类型），构建一个比较它们的内容的查询，运行它，并返回一组具有不同行的类似 diff 的结果。 它不需要表上的主键进行比较。 动态枢轴 返回指向作为第一个参数传递的查询的透视结果的 CURSOR，将排序的标头作为查询作为第二个参数传递。 有关此功能的大量上下文，请参阅 。 global_search / global_regexp_search 两个 plpgsql 函数，用于在整个数据库的所有或部分表中查找字符串或正则表达式的出现。 它返回包含该值的行的表、列和ctid ，以及在正则表达式搜索的情况下的值本身。 搜索可以限于表和/或模式的阵列。 可选择通过发出raise info消息来报告进度。 汉明重量 返回字节
PostgreSQL简单函数创建
01-12
pgsql 自定义函数使用C动态库生成, 非常简单的一个示例。
postgreSql创建自定义函数
jinxiumeihappy的专栏
03-27 2050
1.求两点之间的距离（米） create or replace function st_distance3857(geom1 geometry,geom2 geometry) returns float as $$ select st_distance( st_transform(geom1,3857), st_transform(geom2,3857) ) ...
postgresql创建、删除函数
最新发布
weixin_58494422的博客
06-18 442
在PostgreSQL中，创建函数使用CREATE FUNCTION语句，删除函数使用DROP FUNCTION语句。
PostgreSQL创建函数
linyou?
12-03 1424
PostgreSQL创建函数
PostgreSQL PL/SQL写法和用法（plpgsql）
RaynorFTD的博客
11-03 5796
一、概述： PL/pgSQL函数在第一次被调用时，其函数内的源代码(文本)将被解析为二进制指令树，但是函数内的表达式和SQL命令只有在首次用到它们的时候，PL/pgSQL解释器才会为其创建一个准备好的执行规划，随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中，有部分SQL命令或表达式没有被用到，那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划，这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间，然而缺点是某些表达式或SQL命令中的错误只有在其被
postgresql-10.12-1-linux-x64-binaries.tar.gz
05-21
7. **扩展性：** 允许用户自定义函数、操作符、索引方法和数据类型，方便开发特定领域的应用。 8. **安全性：** 提供角色管理、权限控制、加密功能，确保数据安全。 9. **性能优化：** 自动统计分析、自动调优、...
Swift中的PostgreSQL函数-Swift开发
05-27
PL / Swift PL / Swift允许您使用Swift编程语言为PostgreSQL数据库服务器编写自定义SQL函数和类型。 将Swift带到后端的后端一个小教程可以是PL / Swift PL / Swift允许您使用Swift编程语言为PostgreSQL数据库服务器...
postgresql汉字转拼音函数
04-28
支持2万＋汉字转拼音
postgresql的如下版本postgresql-10.16-1-windows-x64.rar
03-02
10. **PL/pgSQL和自定义函数**：支持过程语言PL/pgSQL，允许创建复杂的存储过程，同时支持创建自定义函数，增强了数据库的灵活性。 在安装“postgresql-10.16-1-windows-x64.exe”这个压缩包时，你需要遵循以下步骤...
Postgresql - Query Language (SQL) Functions
chuckchen1222的博客
08-29 444
SQL函数是执行SQL语句的一个列表，返回列表中的最后一个查询结果。在简单（non-set）情况下，将返回最后一个结果的第一行。如果最后一个查询完全不返回行，则返回空值。 或者，可以声明SQL函数返回一个集合，通过将函数返回的类型指定为SETOF类型，或将其生命为返回表（列），在这种情况下返回最后一个查询结果的所有行。 SQL函数的主体必须是用分号分隔的SQL语句的列表。最后一个语句之后的分好...
PostgreSQL C Language Function 常用类型对照
kmblack1的专栏
11-05 1889
C语言写PostgreSQL函数时,获取参数的对照表 switch (typid) { case ABSTIMEOID: elog(NOTICE, "%d", DatumGetAbsoluteTime(dtmp)); break; case INT8OID: elog(NOTICE
PostgreSQL学习总结（二十）——(PL/pgSQL过程语言)
Gorden的博客
08-12 2054
一、概述： PL/pgSQL函数在第一次被调用时，其函数内的源代码(文本)将被解析为二进制指令树，但是函数内的表达式和SQL命令只有在首次用到它们的时候，PL/pgSQL解释器才会为其创建一个准备好的执行规划，随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中，有部分SQL命令或表达式没有被用到，那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划，这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间，然而缺点是某些表达式或SQL命令中的错误
drop function
日子头上一把刀--DRY&&KISS
08-05 532
在pgsql中 如果要删除函数，除了必须是这个函数的主人以外，还要指定函数的参数类型。 DROP FUNCTION calculate_current_maintenance_date ( company_id companies.id%TYPE ); ...
PostgreSQL中 AnyElement AnyArray AnynonArray的区别与联系
weixin_34310785的博客
07-15 425
http://www.postgresql.org/docs/current/static/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC 先看一个例子： CREATE OR REPLACE FUNCTION myappend01(anyelement, anyelement) RETURNS anyelement AS $$ SELE...
postgresql之do language 执行匿名块
gaojiebao的博客
12-15 1063
postgresql/sql-do/plgsql
摘要:PostgreSQL开发技术基础:过程与函数
xuejianxinokok的专栏
05-07 1225
这减轻了行更新的实际顺序的不可预见性的影响，并且意味着RETURNING数据是在不同WITH子语句和主查询之间传达改变的唯一方法。并不是所有的SQL都是有效的PL / PgSQL，正如你发现的那样，例如，你不能在没有INTO或RETURN QUERY情况下使用SELECT。WITH中的DELETE删除来自products的指定行，以RETURNING子句返回被删除的内容，然后主查询读该输出并将它插入到products_log。在大多数情况下，当你认为你需要PL / PgSQL时，你可能不需要。
【PostgreSQL】PostgreSQL删除带参数的函删除同名参数不同的函数
tttzzzqqq2018的博客
11-28 565
【代码】【PostgreSQL】PostgreSQL删除带参数的函删除同名参数不同的函数。
[PG] postgresql 存储过程示例
啊嘞嘞的博客
06-10 1246
PostgreSQL函数也称为PostgreSQL存储过程。 PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明，分配，循环，控制流程等)。 它有助于您执行通常在数据库中的单个函数中进行多次查询和往返操作的操作。 您可以在许多语言(如SQL，PL/pgSQL，C，Python等)中创建PostgreSQL函数 CREATE [OR R...
postgresql 自定义函数可以写着from后吗
07-14
是的，PostgreSQL的自定义函数可以写在FROM子句后面。在这种情况下，函数将作为一个表达式被调用，并且可以在查询中使用返回的结果。这通常用于将函数的结果与其他表或子查询进行联接或过滤。 例如，以下是一个简单的示例，演示了如何在FROM子句后使用自定义函数： ```sql SELECT * FROM my_function() AS result; ``` 在上面的示例中，my_function()是一个自定义函数，它返回一个结果集。通过将其放置在FROM子句后并使用AS关键字指定别名，可以将函数的结果作为一个临时表使用，并在查询中引用它。 需要注意的是，自定义函数必须返回一个结果集才能在FROM子句中使用。否则，将会出现语法错误。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值