| SET datestyle = "ISO, DMY"; |
| -- Test heuristic to resolve unknown-type literals when there are ambiguous |
| -- functions, added in PostgreSQL commit 1a8b9fb549. |
| create function ambigargfunc(a int4, b int4) returns text as $$ select 'int variant chosen'::text $$ language sql; |
| create function ambigargfunc(a int4, b timestamp) returns text as $$ select 'timestamp variant chosen'::text $$ language sql; |
| select ambigargfunc(1,'2'); |
| ambigargfunc |
| -------------------- |
| int variant chosen |
| (1 row) |
| |
| drop table if exists test; |
| NOTICE: table "test" does not exist, skipping |
| create table test (a integer, b integer) distributed by (a); |
| insert into test select a, a%25 from generate_series(1,100) a; |
| select greatest(a,b) from test order by a; |
| greatest |
| ---------- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
| 44 |
| 45 |
| 46 |
| 47 |
| 48 |
| 49 |
| 50 |
| 51 |
| 52 |
| 53 |
| 54 |
| 55 |
| 56 |
| 57 |
| 58 |
| 59 |
| 60 |
| 61 |
| 62 |
| 63 |
| 64 |
| 65 |
| 66 |
| 67 |
| 68 |
| 69 |
| 70 |
| 71 |
| 72 |
| 73 |
| 74 |
| 75 |
| 76 |
| 77 |
| 78 |
| 79 |
| 80 |
| 81 |
| 82 |
| 83 |
| 84 |
| 85 |
| 86 |
| 87 |
| 88 |
| 89 |
| 90 |
| 91 |
| 92 |
| 93 |
| 94 |
| 95 |
| 96 |
| 97 |
| 98 |
| 99 |
| 100 |
| (100 rows) |
| |
| select least(a,b) from test order by a; |
| least |
| ------- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 0 |
| (100 rows) |
| |
| CREATE OR REPLACE FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $1 + $2; $$ LANGUAGE SQL; |
| SELECT add_em(75, 25) AS answer; |
| answer |
| -------- |
| 100 |
| (1 row) |
| |
| SELECT add_em(25, 75, 100) AS answer; |
| ERROR: function add_em(integer, integer, integer) does not exist |
| LINE 1: SELECT add_em(25, 75, 100) AS answer; |
| ^ |
| HINT: No function matches the given name and argument types. You might need to add explicit type casts. |
| CREATE or replace FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $12 + $20; $$ LANGUAGE SQL; |
| ERROR: there is no parameter $12 |
| LINE 1: ...em(integer, integer) RETURNS integer AS $$ SELECT $12 + $20;... |
| ^ |
| CREATE or replace FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $1 + $2 + $3; $$ LANGUAGE SQL; |
| ERROR: there is no parameter $3 |
| LINE 1: ..., integer) RETURNS integer AS $$ SELECT $1 + $2 + $3; $$ LAN... |
| ^ |
| CREATE or replace FUNCTION add_em(integer, integer, integer) RETURNS integer AS $$ SELECT $1 + $2; $$ LANGUAGE SQL; |
| SELECT add_em(25, 75, 100) AS answer; |
| answer |
| -------- |
| 100 |
| (1 row) |
| |
| SELECT add_em(25, 75) AS answer; |
| answer |
| -------- |
| 100 |
| (1 row) |
| |
| SELECT add_em(25) AS answer; |
| ERROR: function add_em(integer) does not exist |
| LINE 1: SELECT add_em(25) AS answer; |
| ^ |
| HINT: No function matches the given name and argument types. You might need to add explicit type casts. |
| create table bank_ac(acno int, name char(20), balance numeric) distributed by (acno); |
| insert into bank_ac values(1,'anne',1000); |
| insert into bank_ac values(3,'chinni',3000); |
| insert into bank_ac values(4,'dany',4000); |
| insert into bank_ac values(5,'eric',5000); |
| insert into bank_ac values(6,'frank',6000); |
| create or replace function ac_debit1(int,numeric) returns integer AS ' update bank_ac set balance = balance - $2 where acno=$1; select 1;' language sql; |
| select ac_debit1(2,100); |
| ac_debit1 |
| ----------- |
| 1 |
| (1 row) |
| |
| create or replace function ac_debit(int,numeric) returns numeric as 'UPDATE bank_ac set balance = balance - $2 where acno = $1; select balance from bank_ac where acno = $1;' language sql; |
| select ac_debit(4, 500); |
| ac_debit |
| ---------- |
| 3500 |
| (1 row) |
| |
| select * from bank_ac order by acno; |
| acno | name | balance |
| ------+----------------------+--------- |
| 1 | anne | 1000 |
| 3 | chinni | 3000 |
| 4 | dany | 3500 |
| 5 | eric | 5000 |
| 6 | frank | 6000 |
| (5 rows) |
| |
| create or replace function ac_credit(int,numeric) returns numeric as 'UPDATE bank_ac set balance = balance + $2 where acno = $1; select balance from bank_ac where acno = $1;' language sql; |
| select ac_credit(2, 800); |
| ac_credit |
| ----------- |
| |
| (1 row) |
| |
| create or replace function ac_delete(int) returns int AS 'delete from bank_ac where acno=$1;' 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 "ac_delete" |
| create or replace function ac_delete(int) returns int AS 'delete from bank_ac where acno=$1; select 1;' language sql; |
| select ac_delete(1); |
| ac_delete |
| ----------- |
| 1 |
| (1 row) |
| |
| select * from bank_ac where acno = 1; |
| acno | name | balance |
| ------+------+--------- |
| (0 rows) |
| |
| create table emp_fun(name text, salary numeric, age integer, cubicle point) distributed by (name); |
| insert into emp_fun values ( 'apple',1000,23,'(1,1)'); |
| insert into emp_fun values ( 'bill',2000,25,'(1,2)'); |
| insert into emp_fun values ( 'cathy',3000,22,'(2,1)'); |
| insert into emp_fun values ( 'drill',3400,45,'(2,2)'); |
| create or replace function increment(emp_fun) returns numeric as ' select $1.salary*2 as salary; ' language sql; |
| select * from emp_fun order by name; |
| name | salary | age | cubicle |
| -------+--------+-----+--------- |
| apple | 1000 | 23 | (1,1) |
| bill | 2000 | 25 | (1,2) |
| cathy | 3000 | 22 | (2,1) |
| drill | 3400 | 45 | (2,2) |
| (4 rows) |
| |
| select name, increment(emp_fun) as new_sal from emp_fun where emp_fun.name='bill'; |
| name | new_sal |
| ------+--------- |
| bill | 4000 |
| (1 row) |
| |
| select name, increment(emp_fun.*) AS new_sal from emp_fun where emp_fun.cubicle ~= point '(2,1)'; |
| name | new_sal |
| -------+--------- |
| cathy | 6000 |
| (1 row) |
| |
| select name, increment(ROW(name,salary*1.1,age,cubicle)) AS new_sal from emp_fun order by name; |
| name | new_sal |
| -------+--------- |
| apple | 2200.0 |
| bill | 4400.0 |
| cathy | 6600.0 |
| drill | 7480.0 |
| (4 rows) |
| |
| create or replace function new_emp_fun() returns emp_fun as $$ select text 'henry' AS name, 1000.0 AS salary, 25 AS age, point '(2,2)' AS cubicle; $$ language sql; |
| select new_emp_fun(); |
| new_emp_fun |
| --------------------------- |
| (henry,1000.0,25,"(2,2)") |
| (1 row) |
| |
| create or replace function new_emp_fun() returns emp_fun AS $$ select ROW('henry',1000.0,25,'(2,2)')::emp_fun; $$ language sql; |
| select new_emp_fun(); |
| new_emp_fun |
| --------------------------- |
| (henry,1000.0,25,"(2,2)") |
| (1 row) |
| |
| select * from new_emp_fun(); |
| name | salary | age | cubicle |
| -------+--------+-----+--------- |
| henry | 1000.0 | 25 | (2,2) |
| (1 row) |
| |
| select (new_emp_fun()).name; |
| name |
| ------- |
| henry |
| (1 row) |
| |
| select new_emp_fun().name; |
| ERROR: syntax error at or near "." |
| LINE 1: select new_emp_fun().name; |
| ^ |
| select name(new_emp_fun()); |
| name |
| ------- |
| henry |
| (1 row) |
| |
| create or replace function get_emp_name(emp_fun) returns text AS $$ select $1.name; $$language sql; |
| select get_emp_name(new_emp_fun()); |
| get_emp_name |
| -------------- |
| henry |
| (1 row) |
| |
| create or replace function add_num2(IN x int, IN y int, OUT sum int) AS 'select $1+$2' language sql; |
| select add_num2(25,35); |
| add_num2 |
| ---------- |
| 60 |
| (1 row) |
| |
| create or replace function add_num2(IN x int, IN y int) AS 'select $1+$2' language sql; |
| ERROR: function result type must be specified |
| create or replace function add_num(IN x int, IN y int, OUT sum int, OUT product int) AS 'select $1+$2, $1*$2' language sql; |
| select add_num(4,5); |
| add_num |
| --------- |
| (9,20) |
| (1 row) |
| |
| create or replace function add_num(x int, y int, sum int, product int) returns integer AS 'select $1+$2, $1*$2;' language sql; |
| ERROR: return type mismatch in function declared to return integer |
| DETAIL: Final statement must return exactly one column. |
| CONTEXT: SQL function "add_num" |
| create type sum_prod AS (sum int, product int); |
| create or replace function num_add_prod(int,int) returns sum_prod AS 'select $1 + $2, $1*$2' language sql; |
| select num_add_prod(10,12); |
| num_add_prod |
| -------------- |
| (22,120) |
| (1 row) |
| |
| create or replace function add_inout(IN x int, INOUT y int) AS 'select $1+$2' language sql; |
| select add_inout(4,8); |
| add_inout |
| ----------- |
| 12 |
| (1 row) |
| |
| create table tab_sour(tabid int, tabsubid int, tabname text) distributed by (tabid); |
| insert into tab_sour values(1,1,'joe'); |
| insert into tab_sour values(1,2,'ed'); |
| insert into tab_sour values(2,1,'mary'); |
| select * from tab_sour; |
| tabid | tabsubid | tabname |
| -------+----------+--------- |
| 1 | 1 | joe |
| 1 | 2 | ed |
| 2 | 1 | mary |
| (3 rows) |
| |
| create or replace function get_tab(int) returns tab_sour AS $$ select * from tab_sour where tabid=$1; $$ language sql; |
| select *, upper(tabname) from get_tab(1) as t1; |
| tabid | tabsubid | tabname | upper |
| -------+----------+---------+------- |
| 1 | 1 | joe | JOE |
| (1 row) |
| |
| create or replace function set_tab(int) returns SETOF tab_sour as $$ select * from tab_sour where tabid=$1; $$ language sql; |
| select * from set_tab(1) as new_tab; |
| tabid | tabsubid | tabname |
| -------+----------+--------- |
| 1 | 1 | joe |
| 1 | 2 | ed |
| (2 rows) |
| |
| create table fun_tree(name text, parent text) distributed by (name); |
| insert into fun_tree values('grand_parent',''); |
| insert into fun_tree values('parent1','grand_parent'); |
| insert into fun_tree values('parent2','grand_parent'); |
| insert into fun_tree values('parent3','grand_parent'); |
| insert into fun_tree values('child1','parent1'); |
| insert into fun_tree values('child2','parent1'); |
| insert into fun_tree values('child3','parent2'); |
| insert into fun_tree values('child4','parent3'); |
| select * from fun_tree; |
| name | parent |
| --------------+-------------- |
| grand_parent | |
| parent1 | grand_parent |
| child1 | parent1 |
| child4 | parent3 |
| parent3 | grand_parent |
| child3 | parent2 |
| parent2 | grand_parent |
| child2 | parent1 |
| (8 rows) |
| |
| create or replace function child_list(text) returns SETOF text AS $$ select name FROM fun_tree where parent =$1 order by name $$ language sql; |
| select child_list('grand_parent'); |
| child_list |
| ------------ |
| parent1 |
| parent2 |
| parent3 |
| (3 rows) |
| |
| select name, child_list(name) from fun_tree; |
| ERROR: function cannot execute on a QE slice because it accesses relation "public.fun_tree" (seg0 slice1 krajaraman:25432 pid=57476) |
| CONTEXT: SQL function "child_list" during startup |
| create or replace function fun_array(anyelement,anyelement) returns anyarray AS $$ select array[$1,$2]; $$ language sql; |
| select fun_array(25,50) as intarray, fun_array('abc'::text, 'bcf') AS textarray; |
| intarray | textarray |
| ----------+----------- |
| {25,50} | {abc,bcf} |
| (1 row) |
| |
| select fun_array(25,50) as intarray, fun_array('abc', 'bcf') AS textarray; |
| ERROR: could not determine polymorphic type because input has type unknown |
| create or replace function fun_is_greater(anyelement,anyelement) returns boolean AS $$ select $1 > $2 $$ language sql; |
| select fun_is_greater(3,5); |
| fun_is_greater |
| ---------------- |
| f |
| (1 row) |
| |
| select fun_is_greater(7,5); |
| fun_is_greater |
| ---------------- |
| t |
| (1 row) |
| |
| create or replace function invalid_func() returns anyelement as $$ select 1; $$ language sql; |
| ERROR: cannot determine result data type |
| DETAIL: A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. |
| create or replace function fun_dup(f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS 'select $1, array[$1,$1]' language sql; |
| select * from fun_dup(22); |
| f2 | f3 |
| ----+--------- |
| 22 | {22,22} |
| (1 row) |
| |
| select current_database(); |
| current_database |
| ------------------ |
| regression |
| (1 row) |
| |
| select current_schema(); |
| current_schema |
| ---------------- |
| public |
| (1 row) |
| |
| select current_schemas(true); |
| current_schemas |
| --------------------- |
| {pg_catalog,public} |
| (1 row) |
| |
| select current_schemas(false); |
| current_schemas |
| ----------------- |
| {public} |
| (1 row) |
| |
| select current_setting('datestyle'); |
| current_setting |
| ----------------- |
| ISO, DMY |
| (1 row) |
| |
| SELECT round(4, 2); |
| round |
| ------- |
| 4.00 |
| (1 row) |
| |
| SELECT round(4, 0); |
| round |
| ------- |
| 4 |
| (1 row) |
| |
| SELECT round(4, -1); |
| round |
| ------- |
| 0 |
| (1 row) |
| |
| SELECT round(4); |
| round |
| ------- |
| 4 |
| (1 row) |
| |
| SELECT round(4, 1+1); |
| round |
| ------- |
| 4.00 |
| (1 row) |
| |
| SELECT round(CAST (4 AS numeric), 4); |
| round |
| -------- |
| 4.0000 |
| (1 row) |
| |
| SELECT round(4.0, 4); |
| round |
| -------- |
| 4.0000 |
| (1 row) |
| |
| SELECT round(0.998, 2); |
| round |
| ------- |
| 1.00 |
| (1 row) |
| |
| SELECT substr('1234', 3); |
| substr |
| -------- |
| 34 |
| (1 row) |
| |
| SELECT substr(varchar '1234', 3); |
| substr |
| -------- |
| 34 |
| (1 row) |
| |
| SELECT substr(CAST (varchar '1234' AS text), 3); |
| substr |
| -------- |
| 34 |
| (1 row) |
| |
| SELECT substr(CAST (varchar '1234' AS text), 3); |
| substr |
| -------- |
| 34 |
| (1 row) |
| |
| SELECT substr(1234, 3); |
| ERROR: function substr(integer, integer) does not exist |
| LINE 1: SELECT substr(1234, 3); |
| ^ |
| HINT: No function matches the given name and argument types. You might need to add explicit type casts. |
| SELECT substr(CAST (1234 AS text), 3); |
| substr |
| -------- |
| 34 |
| (1 row) |
| |
| select * from generate_series(2,4); |
| generate_series |
| ----------------- |
| 2 |
| 3 |
| 4 |
| (3 rows) |
| |
| select * from generate_series(5,1,-2); |
| generate_series |
| ----------------- |
| 5 |
| 3 |
| 1 |
| (3 rows) |
| |
| select * from generate_series(4,3); |
| generate_series |
| ----------------- |
| (0 rows) |
| |
| select date '2007-12-11' + s.a as dates from generate_series(0,14,7) as s(a); |
| dates |
| ------------ |
| 2007-12-11 |
| 2007-12-18 |
| 2007-12-25 |
| (3 rows) |
| |
| CREATE or REPLACE FUNCTION add_one (integer) RETURNS integer AS ' |
| BEGIN |
| RETURN $1 + 1; |
| END; |
| ' LANGUAGE 'plpgsql'; |
| select add_one(4); |
| add_one |
| --------- |
| 5 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION concat_text (text, text) RETURNS text AS ' |
| BEGIN |
| RETURN $1 || $2; |
| END; |
| ' LANGUAGE 'plpgsql'; |
| select concat_text('Cloud','berry'); |
| concat_text |
| ------------- |
| Cloudberry |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ |
| BEGIN |
| RETURN subtotal * 0.06; |
| END; |
| $$ LANGUAGE plpgsql; |
| select sales_tax(30); |
| sales_tax |
| ----------- |
| 1.8 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION sales_tax_new(real) RETURNS real AS $$ |
| DECLARE |
| subtotal ALIAS FOR $1; |
| BEGIN |
| RETURN subtotal * 0.06; |
| END; |
| $$ LANGUAGE plpgsql; |
| select sales_tax_new(40.68); |
| sales_tax_new |
| --------------- |
| 2.4408 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION instr(varchar, integer) RETURNS integer AS $$ |
| DECLARE |
| v_string ALIAS FOR $1; |
| index ALIAS FOR $2; |
| BEGIN |
| RETURN index+10; |
| END; |
| $$ LANGUAGE plpgsql; |
| select instr('next',3); |
| instr |
| ------- |
| 13 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION instr(varchar, integer) RETURNS integer AS $$ |
| DECLARE |
| v_string ALIAS FOR $1; |
| index ALIAS FOR $2; |
| BEGIN |
| RETURN index*10; |
| END; |
| $$ LANGUAGE plpgsql; |
| CREATE or REPLACE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ |
| BEGIN |
| tax := subtotal * 0.06; |
| END; |
| $$ LANGUAGE plpgsql; |
| select sales_tax(30); |
| sales_tax |
| ----------- |
| 1.8 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ |
| BEGIN |
| sum := x + y; |
| prod := x * y; |
| END; |
| $$ LANGUAGE plpgsql; |
| select sum_n_product(12,10); |
| sum_n_product |
| --------------- |
| (22,120) |
| (1 row) |
| |
| 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; |
| END; |
| $$ LANGUAGE plpgsql; |
| select add_three_values(1,2,3); |
| add_three_values |
| ------------------ |
| 6 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, OUT sum anyelement) AS $$ |
| BEGIN |
| sum := v1 + v2 + v3; |
| END; |
| $$ LANGUAGE plpgsql; |
| select add_three_values(1,2,3); |
| add_three_values |
| ------------------ |
| 6 |
| (1 row) |
| |
| create table logtable(a text,b text) distributed by (a); |
| CREATE or REPLACE FUNCTION logfunc1(logtxt text) RETURNS timestamp AS $$ |
| BEGIN |
| INSERT INTO logtable VALUES (logtxt, '2007-12-12 10:00:00.000000'); |
| RETURN '2007-12-12 10:00:00.000000'; |
| END; |
| $$ LANGUAGE plpgsql; |
| select logfunc1('firstrow'); |
| logfunc1 |
| --------------------- |
| 2007-12-12 10:00:00 |
| (1 row) |
| |
| select * from logtable; |
| a | b |
| ----------+---------------------------- |
| firstrow | 2007-12-12 10:00:00.000000 |
| (1 row) |
| |
| CREATE or REPLACE FUNCTION logfunc2(logtxt text) RETURNS timestamp AS $$ |
| DECLARE |
| curtime timestamp; |
| BEGIN |
| curtime := '2007-12-12 10:00:00.000000'; |
| INSERT INTO logtable VALUES (logtxt, curtime); |
| RETURN curtime; |
| END; |
| $$ LANGUAGE plpgsql; |
| select logfunc2('gpdb'); |
| logfunc2 |
| --------------------- |
| 2007-12-12 10:00:00 |
| (1 row) |
| |
| CREATE TABLE db (a INT PRIMARY KEY, b TEXT); |
| CREATE or REPLACE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ |
| BEGIN |
| LOOP |
| UPDATE db SET b = data WHERE a = key; |
| IF found THEN |
| RETURN; |
| END IF; |
| |
| BEGIN |
| INSERT INTO db(a,b) VALUES (key, data); |
| RETURN; |
| EXCEPTION WHEN unique_violation THEN |
| END; |
| END LOOP; |
| END; |
| $$ LANGUAGE plpgsql; |
| SELECT merge_db(1, 'david'); |
| merge_db |
| ---------- |
| |
| (1 row) |
| |
| SELECT merge_db(1, 'dennis'); |
| merge_db |
| ---------- |
| |
| (1 row) |
| |
| CREATE OR REPLACE FUNCTION fib_fun (num integer) RETURNS integer AS $$ |
| BEGIN |
| IF num < 2 THEN |
| RETURN num; |
| END IF; |
| RETURN fib_fun(num - 2) + fib_fun(num - 1); |
| END; |
| $$ LANGUAGE plpgsql; |
| select fib_fun(8); |
| fib_fun |
| --------- |
| 21 |
| (1 row) |
| |
| CREATE OR REPLACE FUNCTION fib_fast(fib_for integer) RETURNS integer AS $$ |
| DECLARE |
| ret integer := 0; |
| nxt integer := 1; |
| tmp integer; |
| BEGIN |
| FOR num IN 1..fib_for LOOP |
| tmp := ret; ret := nxt; nxt := tmp + nxt; |
| END LOOP; |
| RETURN ret; |
| END; |
| $$ LANGUAGE plpgsql; |
| select fib_fast(4); |
| fib_fast |
| ---------- |
| 3 |
| (1 row) |
| |
| CREATE OR REPLACE FUNCTION set_int (lim INTEGER) RETURNS SETOF INTEGER AS $$ |
| BEGIN |
| FOR x in 1..lim LOOP |
| RETURN NEXT x; |
| END LOOP; |
| RETURN; |
| END; |
| $$ LANGUAGE plpgsql; |
| SELECT * FROM set_int(10); |
| set_int |
| --------- |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| (10 rows) |
| |
| CREATE OR REPLACE FUNCTION set_tab1() RETURNS SETOF INTEGER AS $$ |
| DECLARE |
| tab_rec RECORD; |
| BEGIN |
| FOR tab_rec IN SELECT * FROM tab_sour LOOP |
| RETURN NEXT tab_rec.tabid; |
| END LOOP; |
| RETURN; |
| END; |
| $$ LANGUAGE plpgsql; |
| SELECT * FROM set_tab1(); |
| set_tab1 |
| ---------- |
| 1 |
| 1 |
| 2 |
| (3 rows) |
| |
| CREATE OR REPLACE FUNCTION set_tab2() RETURNS SETOF tab_sour AS $$ |
| DECLARE |
| tab_rec RECORD; |
| BEGIN |
| FOR tab_rec IN SELECT * FROM tab_sour LOOP |
| RETURN NEXT tab_rec; |
| END LOOP; |
| RETURN; |
| END; |
| $$ LANGUAGE plpgsql; |
| SELECT * FROM set_tab2(); |
| tabid | tabsubid | tabname |
| -------+----------+--------- |
| 1 | 1 | joe |
| 1 | 2 | ed |
| 2 | 1 | mary |
| (3 rows) |
| |
| CREATE OR REPLACE FUNCTION ref_tab1(tab_cur refcursor) RETURNS refcursor AS $$ |
| BEGIN |
| OPEN tab_cur FOR SELECT * FROM tab_sour; |
| RETURN tab_cur; |
| END; |
| $$ LANGUAGE plpgsql; |
| drop function add_em(integer,integer); |
| drop function add_em(integer,integer,integer); |
| drop function add_num(x integer, y integer, OUT sum integer, OUT product integer); |
| drop type sum_prod cascade; |
| NOTICE: drop cascades to function num_add_prod(integer,integer) |
| drop table bank_ac; |
| drop table emp_fun cascade; |
| NOTICE: drop cascades to 3 other objects |
| DETAIL: drop cascades to function increment(emp_fun) |
| drop cascades to function new_emp_fun() |
| drop cascades to function get_emp_name(emp_fun) |
| drop table tab_sour cascade; |
| NOTICE: drop cascades to 3 other objects |
| DETAIL: drop cascades to function get_tab(integer) |
| drop cascades to function set_tab(integer) |
| drop cascades to function set_tab2() |
| drop table fun_tree; |
| drop table logtable; |
| drop table db; |
| create table stress_source as select a from generate_series(1,100) a; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table stress_table (a int primary key, b int); |
| create or replace function stress_test() returns text as $body$ |
| declare |
| mycounter record; |
| begin |
| truncate stress_table; |
| for mycounter in select a from stress_source order by 1 loop |
| insert into stress_table values(mycounter.a, mycounter.a * 10000); |
| end loop; |
| for mycounter in select a from stress_source order by 1 loop |
| update stress_table set b = b + mycounter.a where a = mycounter.a; |
| end loop; |
| return 'ok'; |
| end; |
| $body$ language plpgsql volatile strict; |
| select stress_test(); |
| stress_test |
| ------------- |
| ok |
| (1 row) |
| |
| select stress_test(); |
| stress_test |
| ------------- |
| ok |
| (1 row) |
| |
| create function bad_ddl() returns void as $body$ |
| begin |
| create table junk_table(a int); |
| drop table junk_table; |
| end; |
| $body$ language plpgsql volatile; |
| select bad_ddl(); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| bad_ddl |
| --------- |
| |
| (1 row) |
| |
| select bad_ddl(); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| bad_ddl |
| --------- |
| |
| (1 row) |
| |
| create or replace function bad_ddl() returns void as $body$ |
| begin |
| execute 'create table junk_table(a int)'; |
| execute 'drop table junk_table'; |
| end; |
| $body$ language plpgsql volatile; |
| select bad_ddl(); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| bad_ddl |
| --------- |
| |
| (1 row) |
| |
| drop function bad_ddl(); |
| SET check_function_bodies TO OFF; |
| CREATE FUNCTION bad_syntax_func() RETURNS INTEGER AS ' |
| BEGIN |
| RTRN 0; |
| END;' |
| LANGUAGE 'plpgsql'; |
| SELECT bad_syntax_func(); |
| ERROR: syntax error at or near "RTRN" |
| LINE 1: RTRN 0 |
| ^ |
| QUERY: RTRN 0 |
| CONTEXT: PL/pgSQL function "bad_syntax_func" line 2 at SQL statement |
| SELECT * from bad_syntax_func(); |
| ERROR: syntax error at or near "RTRN" |
| LINE 1: RTRN 0 |
| ^ |
| QUERY: RTRN 0 |
| CONTEXT: PL/pgSQL function "bad_syntax_func" line 2 at SQL statement |
| DROP FUNCTION bad_syntax_func(); |
| RESET check_function_bodies; |
| CREATE FUNCTION bad_syntax_func() RETURNS INTEGER AS ' |
| BEGIN |
| RTRN 0; |
| END;' |
| LANGUAGE 'plpgsql'; |
| ERROR: syntax error at or near "RTRN" |
| LINE 3: RTRN 0; |
| ^ |
| CONTEXT: SQL statement in PL/PgSQL function "bad_syntax_func" near line 2 |
| CREATE AGGREGATE agg_point_add1 ( |
| basetype=point, |
| SFUNC=point_add, |
| STYPE=point |
| ); |
| create table agg_point_tbl (p point); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause, and no column type is suitable for a distribution key. Creating a NULL policy entry. |
| insert into agg_point_tbl values (POINT(3,5)); |
| insert into agg_point_tbl values (POINT(30,50)); |
| select ' ' as "Expect (33,55)", agg_point_add1(p) from agg_point_tbl; |
| Expect (33,55) | agg_point_add1 |
| ----------------+---------------- |
| | (33,55) |
| (1 row) |
| |
| CREATE AGGREGATE agg_point_add2 ( |
| basetype=point, |
| SFUNC=point_add, |
| STYPE=point, |
| INITCOND='(25, 47)' |
| ); |
| select '' as "Expect (58,102)", agg_point_add2(p) from agg_point_tbl; |
| Expect (58,102) | agg_point_add2 |
| -----------------+---------------- |
| | (58,102) |
| (1 row) |
| |
| create aggregate numeric_sum ( |
| basetype=numeric, |
| SFUNC=numeric_add, |
| STYPE=numeric |
| ); |
| create table agg_numeric_tbl(num numeric) distributed by (num); |
| insert into agg_numeric_tbl VALUES(30); |
| insert into agg_numeric_tbl VALUES(-20); |
| select '' as ten, numeric_sum(num) from agg_numeric_tbl; |
| ten | numeric_sum |
| -----+------------- |
| | 10 |
| (1 row) |
| |
| drop aggregate numeric_sum(numeric); |
| create aggregate numeric_sum_plus_fifty ( |
| basetype=numeric, |
| SFUNC=numeric_add, |
| STYPE=numeric, |
| INITCOND='50' |
| ); |
| select '' as sixty, numeric_sum_plus_fifty(num) from agg_numeric_tbl; |
| sixty | numeric_sum_plus_fifty |
| -------+------------------------ |
| | 60 |
| (1 row) |
| |
| drop aggregate numeric_sum_plus_fifty(numeric); |
| drop table stress_source; |
| drop table stress_table; |
| drop aggregate agg_point_add1(point); |
| drop table agg_point_tbl; |
| drop aggregate agg_point_add2(point); |
| drop table agg_numeric_tbl; |
| -- |
| -- Test for an old bug, where numeric trunc() scribbled on its input. |
| -- |
| do $$ |
| declare |
| n numeric; |
| begin |
| n = repeat('9', 1) || '.12'; |
| |
| raise notice 'n: %', n; |
| raise notice 't: %', trunc(n,1); |
| raise notice 'n: %', n; |
| end; |
| $$; |
| NOTICE: n: 9.12 |
| NOTICE: t: 9.1 |
| NOTICE: n: 9.12 |
| create table invalid_name (name text) distributed replicated; |
| create table candidate (name text, parent_name text) distributed by (name); |
| create or replace function is_invalid_name (aname text) returns boolean language sql as $$ select aname in (select name from invalid_name)$$; |
| create table tst_valid_name (a text) distributed randomly; |
| create table tst_invalid_name (a text) distributed randomly; |
| insert into invalid_name (name) values ('tst_invalid_name'); |
| insert into candidate(name, parent_name) values ('tst_valid_name', 'tst_invalid_name'); |
| select a.name as a_name, |
| a.parent_name as a_parent_name, |
| b.name as b_name, |
| b.parent_name as b_parent_name |
| from candidate a |
| full join (select c.relname as name, |
| 'tst_invalid_name' as parent_name |
| from pg_class c |
| where c.relname like 'tst%' and |
| not is_invalid_name(c.relname)) b |
| on a.name = b.name and |
| a.parent_name = b.parent_name |
| order by 1,2,3,4; |
| ERROR: function cannot execute on a QE slice because it accesses relation "public.invalid_name" |
| CONTEXT: SQL function "is_invalid_name" during startup |
