postgreSQL函数糖

最新推荐文章于 2025-08-05 15:10:16 发布

原创最新推荐文章于 2025-08-05 15:10:16 发布 · 360 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文介绍了SQL在处理大量数据时的优势，强调其在表连接和复杂查询中的简洁性。推荐使用DataGrip作为IDE，以提升工作效率。此外，文章详细讲解了SQL中的变量定义、DO块的使用及函数创建，包括单输出和多输出函数的定义方法。通过实例展示了如何在SQL中定义和调用函数，以及函数的属性如IMMUTABLE、STABLE和VOLATILE的含义。

SQL的编写可以直观数据分布。大部分同学用SQL用的都是select xx from join xx on where。

SQL的优势不在于它的单原子数据处理，当表很多的时候，相似处理的抽象让代码变得简洁。

IDE

推荐DataGrip

编译运行，都很便捷。分分钟解决问题，提升效率

变量

定义变量

declare variable_name data_type [:= expression];

例如：

declare   
   counter    INTEGER := 1;
   first_name VARCHAR(50) := 'John';
   create_at time := NOW();
   payment    NUMERIC(11,2) := 20.5；

使用：

BEGIN 
   RAISE NOTICE '%', created_at;
   PERFORM pg_sleep(10);
   RAISE NOTICE '%', created_at;
END

方法

直接调用：

DO $$

DECARE 变量

BEGIN

代码端

END $$;

DO $$
DECLARE
  now_time time := NOW();
BEGIN
  RAISE NOTICE '%', now_time;
END &&;

函数

函数无非几个方面：

定义，调用，重载。我们分别来讨论。官方网站参考

https://www.postgresql.org/docs/9.1/sql-createfunction.html

函数的定义：

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

先看一个例子：

/*使用 AS 'SQL语句' LANGUAGE SQL*/
CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

第一行声明 add方法，输入两个int值作为变量，输出integer类型

SQL中如果有这个方法，会报错，所以可以使用 CREATE OR REPLACE FUNCTION来定义

第二行定义实现方法，这里使用了sql语言表达方法

AS 'SQL ......' LANGUGE SQL

AS $$ SQL $$ LANGUGE SQL

AS $$ 方法段 $$ LANGUGE SQL

第三行是函数属性，表达了函数是否有side-effect。

VOLATILE（有side-effect，可以修改数据库，相同输入不同时期调用输入返回值也是可变的）

STABLE 不能修改数据库，相同输入时返回相同结果。

IMMUTABLE 没有side-effiect。

方法一多个output定义, 输出字段f1，f2

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;
SELECT * FROM dup(42);
f1|f2
---------------
42|42 is test

方法二定义类型

CREATE TYPE dup_result AS (f1 int, f2 text);
CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

方法三定义TALBLE类型

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

标签

#pgsql