mytest=# select lanname from pg_language;

 lanname 

----------

 internal

 c

 sql

 plpgsql

(4 rows)

mytest=# create table test(id int);

create table

mytest=# insert into test values(generate_series(1,10));

insert 0 10

create or replace function test_f() returns int

as $$

       select id from test;

$$

language sql;

mytest=# select test_f();

 test_f

--------

      1

(1 row)

create or replace function test_f2() returns int

as $$

       select id from test;

       insert into test values(11);

$$

language sql;

报错：

error:  return type mismatch in function declared to return integer

detail:  function's final statement must be select or insert/update/delete returning.

context:  sql function "test_f2"

这种就是可以的,insert放在select前面,执行后test表中就会增加11这条数据

create or replace function test_f3() returns int

as $$

       insert into test values(11);

       select id from test;

$$

language sql;

这种也是类似,在select前面可以放任意sql,不过要确保最后一条是select

create or replace function test_f4() returns int

as $$

       update test set id = 99 where id = 1;

       insert into test values(11);

       select id from test;

$$

language sql;

假如最后一条select不返回任何结果,则返回空

create or replace function test_f6() returns int

as $$

       select id from test where id = 666; --没有666这条数据

$$

language sql;

mytest=# select test_f6();

 test_f6

---------

(1 row)

create or replace function test_f5() returns void

as $$

       update test set id = 99 where id = 1;

       insert into test values(11);

       select id from test;

$$

language sql;

mytest=# select test_f5();

 test_f5

---------

(1 row)

create or replace function test_f6() returns void

as $$

       update test set id = 99 where id = 1;

       insert into test values(11);

       select id from test;

       insert into test values(12);

$$

language sql;

create or replace function test_f7() returns setof int

as $$

       select id from test;

$$

language sql;

create or replace function test_f8() returns setof int

as $$

       delete from test returning *;

$$

language sql;

同理,不加setof返回第一条数据

create or replace function test_f9() returns int

as $$

       delete from test returning *;

$$

language sql;

mytest=# create table test1(id int,info text);

create table

mytest=# create table test2(coo test1,t_time timestamp );

create table

mytest=# insert into test2 values((1,'tom'),now());

insert 0 1

mytest=# select * from test2;

   coo   |           t_time          

---------+----------------------------

 (1,tom) | 2020-08-04 11:34:11.209674

(1 row)

同访问复合类型一样，注意需要添加括号：

mytest=# select (coo).id,(coo).info,t_time from test2;

 id | info |           t_time          

----+------+----------------------------

  1 | tom  | 2020-08-04 11:34:11.209674

(1 row)

create or replace function test_f10() returns test

as $$

       select * from test;

$$

language sql;

这里没有定义为集合，则返回第一条

create or replace function test_f11() returns setof test

as $$

       select * from test;

$$

language sql;

定义返回集合，则返回所有行数

create or replace function test_f12() returns table(id int, info text)

as $$

       select * from test limit 10;

$$

language sql;

返回“表”

mytest=# select * from  test_f12();

 id |  info 

----+--------

  1 | 1test

  2 | 2test

  3 | 3test

  4 | 4test

  5 | 5test

  6 | 6test

  7 | 7test

  8 | 8test

  9 | 9test

 10 | 10test

(10 rows)

假如定义的返回的类型和实际返回的类型不一致会报错

mytest=# \d test1

               table "public.test1"

 column |  type   | collation | nullable | default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

 info   | text    |           |          |

create or replace function test_f12() returns setof test1

as $$

       select id from test1;

$$

language sql;

error:  return type mismatch in function declared to return test1

detail:  final statement returns too few columns.

context:  sql function "test_f12"

create or replace function test_f13(v_relname text) returns void

as $$

       insert into $1 values (998);

$$

language sql;

error:  syntax error at or near "$1"

line 3: insert into $1 values (998);

create or replace function test_f14(v_id int) returns void

as $$

       insert into test values ($1);

$$

language sql;

create or replace function xcc_test() returns int

as $$

       select 1;

$$ language sql;

create or replace function xcc_test2(v_id int,t_id int) returns int

as $$

       select v_id + t_id;

$$ language sql;

create or replace function xcc_test2(int,int) returns int

as $$

       select $1 + $2;

$$ language sql;

create or replace function add_em2 (in x int, in y int, out sum int)

as $$

       select $1 + $2;

$$

language sql;

mytest=# select add_em2(1,5);

 add_em2

---------

       6

(1 row)

mytest=# select * from add_em2(1,5);

 sum

-----

   6

(1 row)

create or replace function sum_n_product(x int, y int, out sum int, out product int)

as $$

       select $1 + $2, $1 * $2;

$$

language sql;

create or replace function test_f14(v_id int,v_info text) returns void

as $$

       insert into test1 values ($1,$2);

$$

language sql;

mytest=# \df test_f14

                            list of functions

 schema |   name   | result data type |    argument data types    | type

--------+----------+------------------+---------------------------+------

 public | test_f14 | void             | v_id integer              | func

 public | test_f14 | void             | v_id integer, v_info text | func

(2 rows)

mytest=# drop function test_f14(int,text);

DROP FUNCTION;

mytest=# \df+ test

或者

mytest=# \sf test(int)

或者

mytest=# select pg_get_functiondef('test'::regproc);

create or replace function test_tmp() returns setof test1

as $$

begin

       select 1;

end ;

$$ language plpgsql volatile ;

比如我们经常使用的now()函数，用来获取时间戳，now()是返回当前事务开始的时间

mytest=# select proname, CASE

  WHEN provolatile = 'i' THEN 'immutable'

  WHEN provolatile = 's' THEN 'stable'

  WHEN provolatile = 'v' THEN 'volatile' end as volatile

  from pg_proc where proname = 'now';

 proname | volatile

---------+----------

 now     | stable

(1 row)

mytest=# begin;

BEGIN

mytest=# select now();

              now             

-------------------------------

 2020-08-08 22:45:02.454335+08

(1 row)

mytest=# select now();

              now             

-------------------------------

 2020-08-08 22:45:02.454335+08

(1 row)

mytest=# select now();

              now             

-------------------------------

 2020-08-08 22:45:02.454335+08

(1 row)

可以看到，在一个事务里面，返回的时间戳是一致的。

mytest=# commit ;

COMMIT

mytest=# select now();

              now             

-------------------------------

 2020-08-08 22:45:12.209252+08

(1 row)

mytest=# select now();

              now             

-------------------------------

 2020-08-08 22:45:13.032606+08

(1 row)

而不同事务，返回的时间戳则不是一致的。

再看看volatile函数

mytest=# select proname, CASE

  WHEN provolatile = 'i' THEN 'immutable'

  WHEN provolatile = 's' THEN 'stable'

  WHEN provolatile = 'v' THEN 'volatile' end as volatile

  from pg_proc where proname = 'clock_timestamp';

     proname     | volatile

-----------------+----------

 clock_timestamp | volatile

(1 row)

mytest=# begin;

BEGIN

mytest=# select clock_timestamp();

       clock_timestamp       

------------------------------

 2020-08-08 22:46:39.50264+08

(1 row)

mytest=# select clock_timestamp();

        clock_timestamp       

-------------------------------

 2020-08-08 22:46:40.265795+08

(1 row)

mytest=# select clock_timestamp();

        clock_timestamp       

-------------------------------

 2020-08-08 22:46:40.902451+08

(1 row)

可以看到，即使在一个事务里，返回的时间戳也不是一致的。

mytest=# create table test (id int, info text);

CREATE TABLE

mytest=# insert into test select generate_series(1,100000),random()::text;

INSERT 0 100000

mytest=# create index idx_test_1 on test(id);

CREATE INDEX

create or replace function f_volatile(i_id int) returns text as $$ 

declare 

  result text; 

begin 

  -- update可以用在volatile函数中, 因为UPDATE要修改数据 

  update test set info='new' where id=i_id returning info into result; 

  return result; 

end; 

$$ language plpgsql volatile;

mytest=# select * from f_volatile(1); 

 f_volatile

------------

 new

(1 row)

mytest=# alter function f_volatile ( integer) immutable;

ALTER FUNCTION

mytest=# select * from f_volatile(1); 

ERROR:  UPDATE is not allowed in a non-volatile function

CONTEXT:  SQL statement "update test set info='new' where id=i_id returning info"

PL/pgSQL function f_volatile(integer) line 6 at SQL statement

mytest=# alter function f_volatile ( integer) stable;

ALTER FUNCTION

mytest=# select * from f_volatile(1); 

ERROR:  UPDATE is not allowed in a non-volatile function

CONTEXT:  SQL statement "update test set info='new' where id=i_id returning info"

PL/pgSQL function f_volatile(integer) line 6 at SQL statement

另外，stable和immutable的函数可以走索引

create or replace function f_test() returns numeric as $$ 

declare 

begin 

  return 1.5; 

end; 

$$ language plpgsql volatile; 

mytest=# explain analyze select * from test where id<=f_test()::int;

                                               QUERY PLAN                                               

---------------------------------------------------------------------------------------------------------

 Seq Scan on test  (cost=0.00..27142.00 rows=33333 width=36) (actual time=0.074..178.699 rows=2 loops=1)

   Filter: (id <= (f_test())::integer)

   Rows Removed by Filter: 99998

 Planning Time: 0.051 ms

 Execution Time: 178.758 ms

(5 rows)

mytest=# alter function f_test ( ) stable;

ALTER FUNCTION

mytest=# explain analyze select * from test where id<=f_test()::int;

                                                       QUERY PLAN                                                       

-------------------------------------------------------------------------------------------------------------------------

 Index Scan using idx_test_1 on test  (cost=0.54..3519.87 rows=33333 width=36) (actual time=0.013..0.014 rows=2 loops=1)

   Index Cond: (id <= (f_test())::integer)

 Planning Time: 0.150 ms

 Execution Time: 0.027 ms

(4 rows)

mytest=# alter function f_test ( ) immutable;

ALTER FUNCTION

mytest=# explain analyze select * from test where id<=f_test()::int;

                                                       QUERY PLAN                                                       

-------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on test  (cost=626.62..1685.29 rows=33333 width=36) (actual time=0.014..0.017 rows=2 loops=1)

   Recheck Cond: (id <= 2)

   Heap Blocks: exact=2

   ->  Bitmap Index Scan on idx_test_1  (cost=0.00..618.29 rows=33333 width=0) (actual time=0.009..0.009 rows=2 loops=1)

         Index Cond: (id <= 2)

 Planning Time: 0.139 ms

 Execution Time: 0.033 ms

(7 rows)

再回到之前说的函数索引，为什么需要immutable的函数才能创建函数索引

先创建一个随机返回1 ~ 25整数的函数

create or replace function myfunc_v(id int) returns int

as $$

declare

       result int;

begin

       select floor(random()*(25-10)+1) * id into result;

       return result;

end;

$$ language plpgsql;

每次调用，返回的值是不一样的，虽然传的参数都是1：

f_test=# select myfunc_v(1);

 myfunc_v

----------

       10

(1 row)

f_test=# select myfunc_v(1);

 myfunc_v

----------

       13

(1 row)

f_test=# select myfunc_v(1);

 myfunc_v

----------

       24

(1 row)

即使在一个事务里，返回的事也每次都不同

f_test=# begin;

BEGIN

f_test=# select myfunc_v(1);

 myfunc_v

----------

       11

(1 row)

f_test=# select myfunc_v(1);

 myfunc_v

----------

       16

(1 row)

f_test=# select myfunc_v(1);

 myfunc_v

----------

       18

(1 row)

f_test=# create index test_idx on test3(myfunc_v(id));

CREATE INDEX

函数索引一旦建立后, 如果执行计划走索引扫描, PostgreSQL必须确保在任何情况下,函数得到的结果和建立函数索引时得到的结果是一致的,否则就会出现走索引检索到的和走全表扫描检索到的结果不一致.这可是灾难性的BUG.

根据select myfunc_v(1)，假设第一次创建函数索引的时候根据id计算出来的值是10，ctid = (1,1)；第二次真正查询的时候，根据你传进来的值是1，PostgreSQL认为都是1就给你返回ctid = (1,1)了，实际上第二次计算出来的key是13，所以会需要immutable的函数才能创建函数索引。

f_test=# create table test5 (id int,crt_time timestamp(0) with time zone);

CREATE TABLE

f_test=# create index idx_test on test5 (to_char(crt_time,'yyyymmddhh24'));

ERROR:  functions in index expression must be marked IMMUTABLE

f_test=# alter function to_char(timestamp with time zone,text) immutable;

ALTER FUNCTION

f_test=# create index idx_test on test5 (to_char(crt_time,'yyyymmddhh24'));

CREATE INDEX

f_test=# show timezone;

   TimeZone   

---------------

 Asia/Shanghai

(1 row)

f_test=# set timezone to 'PRC';

SET

f_test=# insert into test5 values (1,'2012-01-01 12:00:00');

INSERT 0 1

f_test=# select to_char(crt_time,'yyyymmddhh24') from test5;

  to_char  

------------

 2012010112

(1 row)

f_test=# set timezone to 'GMT';

SET

f_test=# select to_char(crt_time,'yyyymmddhh24') from test5;

  to_char  

------------

 2012010104

(1 row)

f_test=# explain select * from test5 where to_char(crt_time,'yyyymmddhh24')='2012010112';

                                     QUERY PLAN                                    

------------------------------------------------------------------------------------

 Bitmap Heap Scan on test5  (cost=4.23..14.81 rows=10 width=12)

   Recheck Cond: (to_char(crt_time, 'yyyymmddhh24'::text) = '2012010112'::text)

   ->  Bitmap Index Scan on idx_test (cost=0.00..4.23 rows=10 width=0)

         Index Cond: (to_char(crt_time, 'yyyymmddhh24'::text) = '2012010112'::text)

(4 rows)

f_test=# select * from test5 where to_char(crt_time,'yyyymmddhh24')='2012010112';

 id |        crt_time       

----+------------------------

  1 | 2012-01-01 04:00:00+00

(1 row)

f_test=# set enable_indexscan to off;

SET

f_test=# set enable_bitmapscan to off;

SET

因为走全表扫描的话, 这个值to_char(crt_time,'yyyymmddhh24') 将被重新运算,

f_test=# select * from test5 where to_char(crt_time,'yyyymmddhh24')='2012010112';

 id | crt_time

----+----------

(0 rows)

而2012010104这个值才有

f_test=# select * from test5 where to_char(crt_time,'yyyymmddhh24')='2012010104';

 id |        crt_time       

----+------------------------

  1 | 2012-01-01 04:00:00+00

(1 row)

mytest=# \d test1

               Table "public.test1"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

 info   | text    |           |          |

create or replace function mytestf22(v_id int ) returns text

as $$

declare

       tmp text;

begin

       select info_mytest from test1 where id = v_id into tmp;

       return tmp;

end;

$$ language plpgsql;

可以看到，还是可以正常的创建。

mytest=# create or replace function mytestf22(v_id int ) returns text

mytest-# as $$

mytest$# declare

mytest$# tmp text;

mytest$# begin

mytest$# select info_mytest from test1 where id = v_id into tmp;

mytest$# return tmp;

mytest$# end;

mytest$# $$ language plpgsql;

CREATE FUNCTION

mytest=# select mytestf22(1);

ERROR:  column "info_mytest" does not exist

LINE 1: select info_mytest from test1 where id = v_id

               ^

QUERY:  select info_mytest from test1 where id = v_id

CONTEXT:  PL/pgSQL function mytestf22(integer) line 5 at SQL statement

这样可以带来的好处就是可以减少不必要的解析，但带来的坏处就是可能函数里面的bug和一些流程上的错误只有真正触发了才会发现：

create or replace function mytestf22(v_id int ) returns text

as $$

declare

       tmp text;

begin

       if v_id > 2 then

              select info_mytest from test1 where id = v_id into tmp;

              return tmp;

       else

              select info from test1 where id = v_id into tmp;

              return tmp;

       end if;

end;

$$ language plpgsql;

正常调用：

mytest=# select mytestf22(1);

 mytestf22

-----------

 1test

(1 row)

do $$

<>

declare

  counter integer := 0;

begin

   counter := counter + 1;

   raise notice 'the current value of counter is %', counter;

end first_block $$;

执行结果：

notice:  the current value of counter is 1

do

do $$

<>

declare

  counter integer := 0;

begin

   counter := counter + 1;

   raise notice 'the current value of counter is %', counter;

   declare 

       counter integer := 0;

   begin 

       counter := counter + 10;

       raise notice 'the current value of counter in the subblock is %', counter;

       raise notice 'the current value of counter in the outer block is %', outer_block.counter;

   end;

   raise notice 'the current value of counter in the outer block is %', counter;

end;

$$;

执行结果：

notice:  the current value of counter is 1

notice:  the current value of counter in the subblock is 10

notice:  the current value of counter in the outer block is 1

notice:  the current value of counter in the outer block is 1

do

使用双减号加注释,它的作用范围是只能在一行有效。如下： 

-- select 1

使用 /*   */  来加一行或多行注释,如下：

/*

  select 1;

  select 2;

*/

create function test() returns integer

as $$

    declare

       quantity integer := 30;

       --

       -- 如果这边定义为quantity constant integer := 30,会报错。

       --

    begin

       raise notice 'quantity here is %', quantity;     --在这里的数量是30

       quantity := 50;

       --

       -- 创建一个子块

       --

                 declare

                       quantity integer := 80;

                 begin

                       raise notice 'quantity here is %', quantity;  --在这里的数量是80

                 end;

       raise notice 'quantity here is %', quantity;     --在这里的数量是50   

       return quantity;

    end;

$$ language plpgsql;

create or replace function test(subtotal numeric) returns numeric

as $$

begin

       return subtotal * 0.06;

end;

$$ language plpgsql;

create or replace function test(int) returns numeric

as $$

declare

       subtotal alias for $1;

begin

       return subtotal * 0.06;

end;

$$ language plpgsql;

create or replace function test(int) returns numeric

as $$

begin

       return $1 * 0.06;

end;

$$ language plpgsql;

create or replace function mytestf() returns int

as $$

declare

       tmp int default 10;

begin

       return tmp;

end;

$$ language plpgsql;

create or replace function mytestf2(v_id int default 10) returns text

as $$

declare

       tmp text;

begin

       select info from test1 where id = v_id into tmp;

       return tmp;

end;

$$ language plpgsql;

调用的时候可以传参也可以不传参，不传则使用默认值：

mytest=# select mytestf2();

 mytestf2

----------

 10test

(1 row)

mytest=# select mytestf2(1);

 mytestf2

----------

 1test

(1 row)

mytest=# \df mytestf2

                           List of functions

 Schema |   Name   | Result data type |   Argument data types   | Type

--------+----------+------------------+-------------------------+------

 public | mytestf2 | text             | v_id integer DEFAULT 10 | func

(1 row)

create or replace function mytestf3(v_id int default 10) returns text

as $$

declare

       tmp text;

begin

       v_id = 3;

       select info from test1 where id = v_id into tmp;

       return tmp;

end;

$$ language plpgsql;

mytest=# select mytestf3(1);

 mytestf3

----------

 3test

(1 row)

mytest=# select mytestf3(2);

 mytestf3

----------

 3test

(1 row)

create or replace function mytestf4(v_id int default 10,out v_ret text default 'mytest func')

as $$

begin

       select info from test1 where id = v_id into v_ret;

end;

$$ language plpgsql;

create or replace function mytestf6(v_id int default 10,out v_ret text)

as $$

begin

       select info from test1 where id = v_id into v_ret;

       v_ret = 'hello world';

end;

$$ language plpgsql;

create or replace function test(subtotal numeric, out tax numeric)

as $$

begin

       tax := subtotal * 0.06;

end;

$$ language plpgsql;  

create or replace function mytestf5(inout v_id int default 10,inout v_ret text default 'mytest func')

as $$

begin

       select info from test1 where id = v_id into v_ret;

end;

$$ language plpgsql;

mytest=# select * from test1;

 id |  info 

----+--------

  1 | 1test

  2 | 2test

  3 | 3test

  4 | 4test

  5 | 5test

  6 | 6test

  7 | 7test

  8 | 8test

  9 | 9test

 10 | 10test

(10 rows)

若没有99这个结果，则把自己作为out输出了，即99

mytest=# select * from mytestf5(99);

 v_id | v_ret

------+-------

   99 |

(1 row)

若不传参，则和in一样，使用了默认值

mytest=# select * from mytestf5();

 v_id | v_ret 

------+--------

   10 | 10test

(1 row)

若有5这个结果，则把5作为out输出

mytest=# select * from mytestf5(5);

 v_id | v_ret

------+-------

    5 | 5test

(1 row)

create or replace function test(out id int,out info text)

as $$

declare

       test_v_id test1.id%type :=10;

       test_info test1.info%type :='hello';

begin

       id :=test_v_id * 10;

       info :=test_info||'world';

end;

$$ language plpgsql;

出参

create or replace function test2(out id test1.id%type,out info test1.info%type)

as $$

declare

       v_id int :=1;

       v_info text :='xcc_test';

begin

       id :=v_id * 10;

       info :=v_info||'hello world';

end;

$$ language plpgsql;

入参

create or replace function test3(v_id test1.id%type) returns text

as $$

declare

       myresult text;

begin

       select info into myresult from test1 where id = v_id;

       return myresult;

end;

$$ language plpgsql;

入出参

create or replace function test20(in v_id int,out id test1.id%type,inout info test1.info%type)

as $$

begin

       id :=v_id * 10;

       info := info||'hello world';

end;

$$ language plpgsql;

create or replace function test2(myrow test1%rowtype) returns void

as $$

declare

       id int;

       info text;

begin

       id := myrow.id * 1;

       info := myrow.info ||'xiongcancan';

       raise notice 'id,info: %,%',id,info;

end ;

$$ language plpgsql;

可以换成如下：

create or replace function test2(myrow test1) returns void

as $$

declare

       id int;

       info text;

begin

       id := (myrow).id * 1;

       info := (myrow).info ||'xiongcancan';

       raise notice 'id,info: %,%',id,info;

end ;

$$ language plpgsql;

注意传值方式：

mytest=# select test2((10,'hello world'));

NOTICE:  id,info: 10,hello worldxiongcancan

 test2

-------

(1 row)

无法访问系统提供的隐含字段

create or replace function test2(myrow test1) returns void

as $$

declare

       id int;

       info text;

       t_oid int;

begin

       id := (myrow).id * 1;

       info := (myrow).info ||'xiongcancan';

    t_oid :=(myrow).oid;

       raise notice 'id,info,oid: %,%,%',id,info,oid;

end ;

$$ language plpgsql;

mytest=# select * from test2((1,'hello'));

ERROR:  column "oid" not found in data type test1

LINE 1: SELECT (myrow).oid

                ^

QUERY:  SELECT (myrow).oid

CONTEXT:  PL/pgSQL function test2(test1) line 9 at assignment

create or replace function test3() returns test1

as $$

declare

       test1_var test1%rowtype;

begin

       select * from test1 into test1_var ;  

    -- 返回第一条数据

       return test1_var;

end;

$$ language plpgsql;

create or replace function test5() returns test1

as $$

declare

       var record;

begin

       select * from test1 into var ;

    -- 返回第一条数据

       return var;

end;

$$ language plpgsql;

create or replace function test5() returns setof test1

as $$

declare

       var record;

       t_sql text :='select * from test1';

begin

       for var in execute t_sql loop

              return next var;

       end loop;

end;

$$ language plpgsql;

create or replace function test_func2() returns setof test1

as $$

declare

       ret record;

begin

       for ret in execute 'select * from test1' loop

              return next ret;

       end loop;

end;

$$ language plpgsql;

不用execute也行：

create or replace function test5() returns setof test1

as $$

declare

       var record;

       -- t_sql text :='select * from test1';

begin

       for var in select * from test1 loop

              return next var;

       end loop;

end;

$$ language plpgsql;

create or replace function test_func1() returns setof int

as $$

declare

       ret record;

begin

       for ret in execute 'select * from test1' loop

              return next ret.id;

       end loop;

end;

$$ language plpgsql;

假如改成return next ret就会报错：

ERROR:  wrong record type supplied in RETURN NEXT

DETAIL:  Number of returned columns (2) does not match expected column count (1).

CONTEXT:  PL/pgSQL function test_func1() line 6 at RETURN NEXT

create or replace function test6() returns record

as $$

declare

       var record;

begin

       select * from test1 into var limit 1;  

       return var;

end;

$$ language plpgsql;

需要这样调用,因为返回的record没有预先的结构：

mytest=# select * from test6() as (myid int,myinfo text);

 myid | myinfo

------+--------

    1 | 1test

(1 row)

直接select * from test6()会报错：

mytest=# select * from test6();

ERROR:  a column definition list is required for functions returning "record"

LINE 1: select * from test6();

create or replace function test7() returns setof record

as $$

declare

       var record;

       t_sql text :='select * from test1';

begin

       for var in execute t_sql loop  

              return next var;

       end loop;

end;

$$ language plpgsql;

同理需要这样调用：

select * from test7() as (myid int,myinfo text);

create or replace function test6() returns record

as $$

declare

       var record;

begin

       select * from test1 into var limit 1;

       if not found then

              raise exception 'error,no info found';

       end if;

       return var;

end;

$$ language plpgsql;

As each expression and SQL command is first executed in the function, the PL/pgSQL interpreter parses and analyzes the command to create a prepared statement, using the SPI manager's SPI_prepare function. Subsequent visits to that expression or command reuse the prepared statement. Thus, a function with conditional code paths that are seldom visited will never incur the overhead of analyzing those commands that are never executed within the current session. A disadvantage is that errors in a specific expression or command cannot be detected until that part of the function is reached in execution. (Trivial syntax errors will be detected during the initial parsing pass, but anything deeper will not be detected until execution.)

PL/pgSQL (or more precisely, the SPI manager) can furthermore attempt to cache the execution plan associated with any particular prepared statement. If a cached plan is not used, then a fresh execution plan is generated on each visit to the statement, and the current parameter values (that is, PL/pgSQL variable values) can be used to optimize the selected plan. If the statement has no parameters, or is executed many times, the SPI manager will consider creating a generic plan that is not dependent on specific parameter values, and caching that for re-use. Typically this will happen only if the execution plan is not very sensitive to the values of the PL/pgSQL variables referenced in it. If it is, generating a plan each time is a net win. See PREPARE for more information about the behavior of prepared statements.

mytest=# create table test(id int,info text);

CREATE TABLE

mytest=# insert into test select 1,repeat(random()::text,10) from generate_series(1,500000);

INSERT 0 500000

mytest=# insert into test values (2,'test');

INSERT 0 1

mytest=# select id,count(*) from test group by id;

 id | count 

----+--------

  1 | 500000

  2 |      1

(2 rows)

mytest=# create index idx_test_1 on test(id);

CREATE INDEX

mytest=# explain analyze select count(*) from test where id=2;

                                                         QUERY PLAN                                                        

----------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=4.44..4.45 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=1)

   ->  Index Only Scan using idx_test_1 on test  (cost=0.42..4.44 rows=1 width=0) (actual time=0.022..0.023 rows=1 loops=1)

         Index Cond: (id = 2)

         Heap Fetches: 1

 Planning Time: 0.073 ms

 Execution Time: 0.056 ms

(6 rows)

Time: 3.091 ms

mytest=# explain analyze select count(*) from test where id=1;

                                                     QUERY PLAN                                                    

--------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=21360.01..21360.02 rows=1 width=8) (actual time=449.037..449.038 rows=1 loops=1)

   ->  Seq Scan on test  (cost=0.00..20110.01 rows=500001 width=0) (actual time=0.013..281.588 rows=500000 loops=1)

         Filter: (id = 1)

         Rows Removed by Filter: 1

 Planning Time: 0.077 ms

 Execution Time: 449.067 ms

(6 rows)

Time: 453.871 ms

CREATE OR REPLACE FUNCTION f_immutable(i_id integer) RETURNS bigint 

AS $$ 

declare 

       result bigint; 

begin 

       select count(*) into result from test where id=i_id; 

       return result; 

end; 

$$ LANGUAGE plpgsql STRICT IMMUTABLE ;

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 178.087 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 183.562 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 178.387 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 177.079 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 179.910 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 182.556 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 188.073 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 190.739 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 179.006 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 187.267 ms

mytest=# select * from f_immutable(1);

 f_immutable

-------------

      500000

(1 row)

Time: 180.634 ms

mytest=# select * from f_immutable(2);

 f_immutable

-------------

           1

(1 row)

Time: 142.957 ms

可以看到，走了顺序扫描。

create or replace function test() returns void

as $$

begin

       execute 'select 1';

end;

$$ language plpgsql;

create or replace function test_execute() returns setof int

as $$

begin

       return query execute 'select * from test';

end;

$$ language plpgsql;

create or replace function test() returns void

as $$

declare

       id int;

begin

       execute 'select 1 ' into id;

       raise notice 'is %',id;

end;

$$ language plpgsql;

如果是如下则会报错：

create or replace function test() returns void

as $$

declare

       id int;

begin

       execute 'select 1 into id ';

       raise notice 'is %',id;

end;

$$ language plpgsql;

ERROR:  EXECUTE of SELECT ... INTO is not implemented

HINT:  You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS instead.

CONTEXT:  PL/pgSQL function test() line 5 at EXECUTE

如下会报错：

create or replace function f_get_rows(tablename text) returns setof record as

$$

declare

       rec record;

begin

       for rec in select * from $1 loop

              return next rec;

       end loop;

       return;

end

$$ language 'plpgsql';

ERROR:  syntax error at or near "$1"

LINE 6: for rec in select * from $1 loop

create or replace function f_get_rows(tablename text) returns setof record as

$$

declare

       rec record;

       t_sql text :='select * from' ||$1;

begin

       for rec in t_sql loop

              return next rec;

       end loop;

       return;

end

$$ language 'plpgsql';

ERROR:  syntax error at or near "t_sql"

LINE 7: for rec in t_sql loop

create or replace function f_get_rows(tablename text) returns setof record as

$$

declare

       rec record;

begin

       for rec in select * from tablename loop

              return next rec;

       end loop;

       return;

end

$$ language 'plpgsql';

mytest=# select f_get_rows('test1');

ERROR:  relation "tablename" does not exist

LINE 1: select * from tablename

                      ^

QUERY:  select * from tablename

CONTEXT:  PL/pgSQL function f_get_rows(text) line 5 at FOR over SELECT rows

create or replace function f_get_rows2(tablename text) returns setof record as

$$

declare

       rec record;

begin

       for rec in EXECUTE 'select * from ' || tablename loop

              return next rec;

       end loop;

       return;

end

$$ language 'plpgsql';

mytest=# select * from f_get_rows2('test1') as(id int,info text);

 id |  info 

----+--------

  1 | 1test

  2 | 2test

  3 | 3test

  4 | 4test

  5 | 5test

  6 | 6test

  7 | 7test

  8 | 8test

  9 | 9test

 10 | 10test

(10 rows)

if ... then ... end if;

if ... then ... else ... end if;

if ... then ... elsif ... then ... else ... end if;

case ... when ... then ... else ... end case;

case when ... then ... else ... end case;

if search_condition then

statement_list

end if;

if search_condition then

statement_list

else

statement_list

end if;

if search_condition then

    statement_list

elsif search_condition then

statement_list

else

statement_list

end if;

create or replace function if_test(vc_empno numeric)

returns void

as $$

declare

       vn_sal numeric;

begin

    select sal into vn_sal from emp where empno = vc_empno;

    if vn_sal is null then

           raise notice '该员工不存在！';

    elsif vn_sal < 1500 then

           raise notice '老板；请多加点工资';

    else

           raise notice '老板；请少加点工资';    

    end if;

end;

$$ language plpgsql;

create or replace function case_test1(x numeric) returns void

as $$

declare

       msg text;

begin

    case x

    when 1, 2 then

        msg := 'one or two';

    else

        msg := 'other value than one or two';

    end case;

         raise notice 'look it, it is %' ,msg;

end;

$$ language plpgsql;

create or replace function case_test1(x numeric) returns void

as $$

declare

       msg text;

begin

    case 

    when x in(1, 2) then

        msg := 'one or two';

    else

        msg := 'other value than one or two';

    end case;

         raise notice 'look it, it is %' ,msg;

end;

$$ language plpgsql;

[ <> ]

loop

    statements

exit [ label ] [ when boolean-expression ];

end loop [ label ];

create or replace function loop_test_01() returns void

as $$

declare

       n numeric := 0;

begin

       loop

           n := n + 1;

           raise notice 'n 的当前值为: %',n;

           exit when n >= 10;

           -- return;

         end loop;

end;

$$ language plpgsql;

while boolean-expression loop

       statements;

end loop;

create or replace function loop_test_02() returns void

as $$

declare

       n numeric := 0;

begin

    while n < 10 loop

           n := n + 1;

           raise notice 'n 的当前值为: %',n;

    end loop;

end;

$$ language plpgsql;

for name in [ reverse ] expression .. expression [ by expression ] loop

       statements

end loop [ label ];

for i in 1..10 loop

-- 每次加1

end loop;

for i in reverse 10..1 loop

-- 每次减1

end loop;

for i in reverse 10..1 by 2 loop

--- 每次减2

end loop;

create or replace function loop_test_03() returns void

as $$

declare

    i record;

begin

   for i in select v_id,info from test1 limit 10 loop

                 raise notice '% %', i.v_id ,i.info;

   end loop;    

end;

$$ language plpgsql;

continue [ label ] [ when boolean-expression ];

create or replace function loop_test_04() returns void

as $$

declare

       n numeric := 0;

begin

    while n < 10 loop

           n := n + 1;

           continue when n = 5;

           raise notice 'n 的当前值为: %',n;

         end loop;

end;

$$ language plpgsql;

mytest=# select loop_test_04();

NOTICE:  n 的当前值为: 1

NOTICE:  n 的当前值为: 2

NOTICE:  n 的当前值为: 3

NOTICE:  n 的当前值为: 4

NOTICE:  n 的当前值为: 6

NOTICE:  n 的当前值为: 7

NOTICE:  n 的当前值为: 8

NOTICE:  n 的当前值为: 9

NOTICE:  n 的当前值为: 10

exit [ label ] [ when boolean-expression ];

create or replace function loop_test_05() returns void

as $$

declare

       n numeric := 0;

begin

    while n < 10 loop

           n := n + 1;

              exit when n = 5;

              raise notice 'n 的当前值为: %',n;

         end loop;

end;

$$ language plpgsql;

return

return expression;

create or replace function test_ret(int) returns int

as $$

declare

       id int :=10;

       v_id alias for $1;   

-- 别名的另一种方式

begin

       id := id * v_id;

         return id;

end;

$$ language plpgsql;

create or replace function getcompfoo2

(in_col1 in int,in_col2 in text,out_col1 out int, out_col2 out text)

as $$

begin

    out_col1 := in_col1 + 1;

    out_col2 := in_col2 || '_result';

end;

$$ language plpgsql;

create or replace function getreturn(in_col1 int) returns void

as $$

begin

       if in_col1 > 0 then

              raise notice 'there is %',in_col1;

    else

           return;

    end if;

end;

$$ language plpgsql;

return next expression;

create or replace function test5() returns setof test1

as $$

declare

       var record;

       t_sql text :='select * from test1';

begin

       for var in execute t_sql loop

              return next var;

       end loop;

end;

$$ language plpgsql;

return query query;

return query execute command-string [ using expression [, ... ] ];

create or replace function gettest1() returns setof test1

as $$

begin

    return query select * from test1 limit 10;

end

$$ language plpgsql;

这里要注意：

mytest=# select gettest1();

  gettest1  

-------------

 (1,1test)

 (2,2test)

 (3,3test)

 (4,4test)

 (5,5test)

 (6,6test)

 (7,7test)

 (8,8test)

 (9,9test)

 (10,10test)

(10 rows)

mytest=# select * from gettest1();

 id |  info 

----+--------

  1 | 1test

  2 | 2test

  3 | 3test

  4 | 4test

  5 | 5test

  6 | 6test

  7 | 7test

  8 | 8test

  9 | 9test

 10 | 10test

(10 rows)

create or replace function gettest1() returns setof test1

as $$

begin

    return query execute 'select * from test1 limit 10';

end

$$ language plpgsql;

和execute类似，不过要防止SQL注入：

create or replace function gettest2(text) returns setof test1

as $$

begin

    return query execute 'select * from '|| $1 || ' limit 10';

end

$$ language plpgsql;

   curs1 refcursor;

   curs2 cursor for select * from tenk1;

  curs3 cursor (key integer) is select * from test1 where unique1 = key;

create or replace function cursor_test() returns refcursor

as $$

declare

       mycursor refcursor;

begin

       open mycursor for select * from test1 ;

       return mycursor;

end ;

$$ language plpgsql;

或者

create or replace function cursor_test() returns refcursor

as $$

declare

       mycursor refcursor;

       t_sql text := 'select * from test';

begin

       open mycursor for execute t_sql;

       return mycursor;

end ;

$$ language plpgsql;

调用方式：

mytest=# begin;

BEGIN

mytest=# select * from cursor_test();

    cursor_test    

--------------------

(1 row)

fetch all from也可以

mytest=# fetch all in "";

 id

----

  1

  2

  3

  4

  5

  6

  7

  8

  9

 10

 99

(11 rows)

mytest=# commit;

COMMIT

create or replace function cursor_test1(mycursor refcursor) returns refcursor

as $$

begin

       open mycursor for select * from test1 ;

       return mycursor;

end ;

$$ language plpgsql;

执行结果如下：

mytest=# begin;

BEGIN

mytest=# select cursor_test1('mycus');

 cursor_test1

--------------

 mycus

(1 row)

mytest=# fetch next in "mycus";

 id | info 

----+-------