216 lines
4.7 KiB
PL/PgSQL
216 lines
4.7 KiB
PL/PgSQL
CREATE EXTENSION pg_stat_statements;
|
|
|
|
--
|
|
-- simple and compound statements
|
|
--
|
|
SET pg_stat_statements.track_utility = FALSE;
|
|
SET pg_stat_statements.enable_superuser_track = ON;
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
SELECT 1 AS "int";
|
|
|
|
SELECT 'hello'
|
|
-- multiline
|
|
AS "text";
|
|
|
|
SELECT 'world' AS "text";
|
|
|
|
-- transaction
|
|
BEGIN;
|
|
SELECT 1 AS "int";
|
|
SELECT 'hello' AS "text";
|
|
COMMIT;
|
|
|
|
-- compound transaction
|
|
BEGIN \;
|
|
SELECT 2.0 AS "float" \;
|
|
SELECT 'world' AS "text" \;
|
|
COMMIT;
|
|
|
|
-- compound with empty statements and spurious leading spacing
|
|
\;\; SELECT 3 + 3 \;\;\; SELECT ' ' || ' !' \;\; SELECT 1 + 4 \;;
|
|
|
|
-- non ;-terminated statements
|
|
SELECT 1 + 1 + 1 AS "add" \gset
|
|
SELECT :add + 1 + 1 AS "add" \;
|
|
SELECT :add + 1 + 1 AS "add" \gset
|
|
|
|
-- set operator
|
|
SELECT 1 AS i UNION SELECT 2 ORDER BY i;
|
|
|
|
-- ? operator
|
|
select '{"a":1, "b":2}'::jsonb ? 'b';
|
|
|
|
-- cte
|
|
WITH t(f) AS (
|
|
VALUES (1.0), (2.0)
|
|
)
|
|
SELECT f FROM t ORDER BY f;
|
|
|
|
-- prepared statement with parameter
|
|
PREPARE pgss_test (int) AS SELECT $1, 'test' LIMIT 1;
|
|
EXECUTE pgss_test(1);
|
|
DEALLOCATE pgss_test;
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
--
|
|
-- CRUD: INSERT SELECT UPDATE DELETE on test table
|
|
--
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
-- utility "create table" should not be shown
|
|
CREATE TEMP TABLE test (a int, b char(20));
|
|
|
|
INSERT INTO test VALUES(generate_series(1, 10), 'aaa');
|
|
UPDATE test SET b = 'bbb' WHERE a > 7;
|
|
DELETE FROM test WHERE a > 9;
|
|
|
|
-- explicit transaction
|
|
BEGIN;
|
|
UPDATE test SET b = '111' WHERE a = 1 ;
|
|
COMMIT;
|
|
|
|
BEGIN \;
|
|
UPDATE test SET b = '222' WHERE a = 2 \;
|
|
COMMIT ;
|
|
|
|
UPDATE test SET b = '333' WHERE a = 3 \;
|
|
UPDATE test SET b = '444' WHERE a = 4 ;
|
|
|
|
BEGIN \;
|
|
UPDATE test SET b = '555' WHERE a = 5 \;
|
|
UPDATE test SET b = '666' WHERE a = 6 \;
|
|
COMMIT ;
|
|
|
|
-- many INSERT values
|
|
INSERT INTO test (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c');
|
|
|
|
-- SELECT with constants
|
|
SELECT * FROM test WHERE a > 5 ORDER BY a ;
|
|
|
|
SELECT *
|
|
FROM test
|
|
WHERE a > 9
|
|
ORDER BY a ;
|
|
|
|
-- SELECT without constants
|
|
SELECT * FROM test ORDER BY a;
|
|
|
|
-- SELECT with IN clause
|
|
SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5);
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
--
|
|
-- pg_stat_statements.track = none
|
|
--
|
|
SET pg_stat_statements.track = 'none';
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
SELECT 1 AS "one";
|
|
SELECT 1 + 1 AS "two";
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
--
|
|
-- pg_stat_statements.track = top
|
|
--
|
|
SET pg_stat_statements.track = 'top';
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
DO LANGUAGE plpgsql $$
|
|
BEGIN
|
|
-- this is a SELECT
|
|
PERFORM 'hello world'::TEXT;
|
|
END;
|
|
$$;
|
|
|
|
-- PL/pgSQL function
|
|
CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
|
|
DECLARE
|
|
r INTEGER;
|
|
BEGIN
|
|
SELECT (i + 1 + 1.0)::INTEGER INTO r;
|
|
RETURN r;
|
|
END; $$ LANGUAGE plpgsql;
|
|
|
|
SELECT PLUS_TWO(3);
|
|
SELECT PLUS_TWO(7);
|
|
|
|
-- SQL function --- use LIMIT to keep it from being inlined
|
|
CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
|
|
$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
|
|
|
|
SELECT PLUS_ONE(8);
|
|
SELECT PLUS_ONE(10);
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
--
|
|
-- pg_stat_statements.track = all
|
|
--
|
|
SET pg_stat_statements.track = 'all';
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
-- we drop and recreate the functions to avoid any caching funnies
|
|
DROP FUNCTION PLUS_ONE(INTEGER);
|
|
DROP FUNCTION PLUS_TWO(INTEGER);
|
|
|
|
-- PL/pgSQL function
|
|
CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
|
|
DECLARE
|
|
r INTEGER;
|
|
BEGIN
|
|
SELECT (i + 1 + 1.0)::INTEGER INTO r;
|
|
RETURN r;
|
|
END; $$ LANGUAGE plpgsql;
|
|
|
|
SELECT PLUS_TWO(-1);
|
|
SELECT PLUS_TWO(2);
|
|
|
|
-- SQL function --- use LIMIT to keep it from being inlined
|
|
CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
|
|
$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
|
|
|
|
SELECT PLUS_ONE(3);
|
|
SELECT PLUS_ONE(1);
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
--
|
|
-- utility commands
|
|
--
|
|
SET pg_stat_statements.track_utility = TRUE;
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
SELECT 1;
|
|
CREATE INDEX test_b ON test(b);
|
|
DROP TABLE test \;
|
|
DROP TABLE IF EXISTS test \;
|
|
DROP FUNCTION PLUS_ONE(INTEGER);
|
|
DROP TABLE IF EXISTS test \;
|
|
DROP TABLE IF EXISTS test \;
|
|
DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
|
|
DROP FUNCTION PLUS_TWO(INTEGER);
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
/*
|
|
* POLAR: disable super user track
|
|
*/
|
|
SET pg_stat_statements.enable_superuser_track = FALSE;
|
|
SELECT pg_stat_statements_reset();
|
|
|
|
CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
|
|
$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
|
|
|
|
SELECT PLUS_ONE(8);
|
|
SELECT PLUS_ONE(10);
|
|
|
|
DROP FUNCTION PLUS_ONE(INTEGER);
|
|
|
|
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
|
|
|
|
DROP EXTENSION pg_stat_statements;
|