Writing Basic Postgres Functions

1. Writing Basic Postgres Functions By Lloyd Albin 12/3/2013

2. Functions The Basics - Languages

3. Languages • Pre-Installed Languages: • SQL • C • internal • Installable Languages that come with Postgres: • plpgsql • plperl • plperlu • pltcl • plpython • Other Downloadable Languages: • pljava • plphp • plpy • plr • plruby • plscheme • plsh

4. Finding Languages that can be Installed • SELECT name, commentFROM pg_available_extensionsWHERE comment LIKE '%language%';

5. Finding Downloadable Languages • http://www.postgresql.org/docs/9.3/static/external-pl.html • http://www.postgresql.org/docs/9.2/static/external-pl.html • http://www.postgresql.org/docs/9.1/static/external-pl.html • http://www.postgresql.org/docs/9.0/static/external-pl.html • http://www.postgresql.org/docs/8.4/static/external-pl.html

6. How to Install a Language • CREATE EXTENSION plperl; • CREATE EXTENSION plperlu; • CREATE EXTENSION plpgsql;

7. Uninstalling a Language • DROP EXTENSION plperl; • DROP EXTENSION plperlu; • DROP EXTENSION plpgsql;

8. Functions The Basics – Function Behavior

9. IMMUTABLE • IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. • If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. • What this means is these types of functions can be used as a type converter or indexer.

10. STABLE • STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. • This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. • It is inappropriate for AFTER triggers that wish to query rows modified by the current command. • Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

11. VOLATILE • VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. • Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). • But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; some example are random(), currval(), timeofday()

12. Functions The Basics – NULL INPUT

13. What to do with NULL? • CALLED ON NULL INPUT – This is the default if you don’t say anything. You will need to handle the NULL’s within your code. • RETURNS NULL ON NULL INPUT or STRICT – This will cause the function to return NULL when any of the input values are NULL. The body of the function is never executed.

14. Functions The Basics - SECURITY

15. SECURITY INVOKER – This is the default. The function is to be executed with the privileges of the user that calls it. • SECURITY DEFINER – This specifies that the function is to be executed with the privileges of the user that created it. This could be used to have a function update a table that the calling user does not have permissions to access, etc.

16. Functions The Basics - Syntax

17. CREATE FUNCTION CREATE OR REPLACE FUNCTION (argname text ) RETURNS numeric AS $body$ …. $body$ LANGUAGE 'sql' IMMUTABLE | STABLE | VOLATILE RETURNS NULL ON NULL INPUT SECURITY DEFINER ;

18. Additional Return Types • void – This is for when the trigger should not return anything. It is just doing some backgroud process for you. • trigger – This must be set for all trigger functions. • boolean, text, etc – This is for a single values being passed back. • SET OF schema.table – This is for returning multiple rows of data. This can either point to an existing table or a composite type to get the table/field layout.

19. Simple Functions Basic SQL functions – Converting a field type

20. Converting a tables field CREATE TABLE tools.lloyd_test ( mynumber VARCHAR ); ALTER TABLE tools.lloyd_test ALTER COLUMN mynumber TYPE INTEGER COLLATE pg_catalog."default"; ERROR: collations are not supported by type integer

21. Using a function to do the conversion ALTER TABLE tools.lloyd_test ALTER COLUMN mynumber TYPE INTEGER USING tools.chartoint(mynumber);

22. Convert varchar to int CREATE OR REPLACE FUNCTION tools.chartoint( chartoconvertvarchar ) RETURNS integer AS $body$ SELECT CASE WHEN trim(chartoconvert) SIMILAR TO '[0-9,]+' THEN CAST(trim(REPLACE(chartoconvert,',','')) AS integer) ELSE NULL END; $body$ LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

23. Convert varchar to int CREATE OR REPLACE FUNCTION tools.chartonumeric( chartoconvertvarchar ) RETURNS numeric AS $body$ SELECT CASE WHEN trim(chartoconvert) SIMILAR TO '[0-9,.-]+' THEN CAST(trim(REPLACE(chartoconvert,',','')) AS integer) ELSE NULL END; $body$ LANGUAGE 'sql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

24. Simple Functions Basic PL/pgSQL functions – Using as an index

25. The Problem • We receive faxes that are multi-page TIFF’s. The TIFF file name are called the raster id. We have data where we have the full path of the file name, the raster id and the raster id with page number. • Examples: • 0000/000000 • 0000/0000001111 • /studydata/studyname/0000/000000

26. Finding the Raster ID The first thing to do, is to be able to find the Raster ID, no matter which format is supplied. CREATE FUNCTION find_raster (raster varchar) RETURNS VARCHAR(11) AS $$ BEGIN CASE length(raster) WHEN 11 THEN -- Format: 1234/567890 -- Returns: 1234/567890 RETURN raster; WHEN 15 THEN -- Format: 1234/5678901234 -- Returns: 1234/567890 RETURN substr(raster, 1, 11); ELSE -- Format: /study_data/study_name/1234/567890 -- Returns: 1234/567890 RETURN substr(raster, length(raster)-10, 11); END CASE; END; $$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;

27. Examples of the find_raster Function -- Test returning of Raster ID when Submitting Raster ID SELECT find_raster('1234/567890'); -- Returns: 1234/567890 -- Test returning of Raster ID when Submitting Raster ID with 4 Digit Page Number SELECT find_raster('1234/5678901234'); -- Returns: 1234/567890 -- Test returning of Raster ID when Submitting Filename that includes Raster ID SELECT find_raster('/study_data/study_name/1234/567890'); -- Returns: 1234/567890

28. Adding the Index CREATE INDEX [ name ] ON table ( expression ) expression An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call. CREATE INDEX raster_raster_idx ON raster find_raster(raster); CREATE INDEX raster_file_raster_idx ON raster_filefind_raster(raster); CREATE INDEX raster_page_raster_idx ON raster_pagefind_raster(raster);

29. The benefits of the Index Without Index With Index SELECT raster.*, raster_page.* FROM (SELECT * FROM raster OFFSET 50000 LIMIT 100) raster LEFT JOIN raster_page ON raster.raster = find_raster(raster_page.raster); Total runtime: 141.809 ms Total runtime: 0.141809 s SELECT raster.*, raster_page.* FROM (SELECT * FROM raster OFFSET 50000 LIMIT 100) raster LEFT JOIN raster_page ON raster.raster = substr(raster_page.raster, 1, 11); • Total runtime: 4982.527 ms • Total runtime: 4.982527 s

30. Simple Functions Basic PL/pgSQL functions – Triggers

31. Shadow Tables • Sometimes we want to have a copy of a table and know when everything happened to the original table, insert, update, delete, and truncate. This is possible to have happen automatically with a trigger function.

32. Creating the base tables • Table 1 CREATE TABLE public.table1 ( key SERIAL, value INTEGER, value_type VARCHAR, PRIMARY KEY(key) ) ; • Table 2 CREATE TABLE public.table2 ( key INTEGER, value INTEGER, value_type VARCHAR, user_name NAME, action VARCHAR, action_time TIMESTAMP ) ;

33. The Shadow Function CREATE FUNCTION public.shadow_table1 ( ) RETURNS trigger AS $body$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO public.table2 VALUES(NEW.key, NEW.value, NEW.value_type, current_user, TG_OP, now()); RETURN NEW; END IF; IF TG_OP = 'UPDATE' THEN INSERT INTO public.table2 VALUES(NEW.key, NEW.value, NEW.value_type, current_user, TG_OP, now()); RETURN NEW; END IF; IF TG_OP = 'DELETE' THEN INSERT INTO public.table2 VALUES(OLD.key, OLD.value, OLD.value_type, current_user, TG_OP, now()); RETURN OLD; END IF; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

34. Adding the trigger to the table CREATE TRIGGER table1_tr BEFORE INSERT OR UPDATE OR DELETE ON public.table1 FOR EACH ROW EXECUTE PROCEDURE public.shadow_table1();

35. Working with Table1 INSERT INTO public.table1 (value, value_type) VALUES ('30', 'meters'); INSERT INTO public.table1 (value, value_type) VALUES ('10', 'inches'); UPDATE public.table1 SET value = '20' WHERE value_type = 'inches'; DELETE FROM public.table1 WHERE value_type = 'inches'; INSERT INTO public.table1 (value, value_type) VALUES ('50', 'inches');

36. What they look like Table1 Table2

37. Simple Functions Basic PL/pgSQL functions – Write to a hidden table

38. Hidden Tables • Sometimes you may want a normal user to be able to write a table, but that user also not be able to view/select any contents from the table aka INSERT only. • This does not play with some front end applications. • Some people will write functions where you pass in the variables, but that is not always possible depending on the front end that is being written.

39. Creating the base tables • Table 1 SET ROLE a; CREATE TABLE public.table1 ( key SERIAL, value INTEGER, value_type VARCHAR, PRIMARY KEY(key) ) ; • Table 2 SET ROLE b; CREATE TABLE public.table2 ( key INTEGER, value INTEGER, value_typeVARCHAR, PRIMARY KEY(key) ) ;

40. The Hidden Function SET ROLE b; CREATE FUNCTION public.hidden_table1 ( ) RETURNS trigger AS $body$ BEGIN INSERT INTO public.table2 VALUES(NEW.key, NEW.value, NEW.value_type); RETURN NULL; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

41. Adding the trigger to the table CREATE TRIGGER table1_tr BEFORE INSERT ON public.table1 FOR EACH ROW EXECUTE PROCEDURE public.hidden_table1();

42. Working with Table1 INSERT INTO public.table1 (value, value_type) VALUES ('30', 'meters'); INSERT INTO public.table1 (value, value_type) VALUES ('10', 'inches'); INSERT INTO public.table1 (value, value_type) VALUES ('50', 'inches');

43. What they look like Table1 Table2

44. Simple Functions Basic PL/pgSQL functions – Returning a Table

45. Counting the tables CREATE OR REPLACE FUNCTION tools.count_schema_rows(search_schema_name name) RETURNS SETOF tools.schema_row_count AS $body$ DECLARE schema_resultsRECORD; table_results RECORD; BEGIN FOR schema_results IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = search_schema_name AND table_type = 'BASE TABLE' ORDER BY table_name LOOP -- looping through tables in schema here EXECUTE 'SELECT ' || quote_literal(schema_results.table_schema) || '::NAME AS schema_name, ' || quote_literal(schema_results.table_name) || '::NAME AS table_name, count(*), pg_total_relation_size(''' || schema_results.table_schema || '.' || schema_results.table_name || ''') AS total_size, pg_size_pretty(pg_total_relation_size(''' || schema_results.table_schema || '.' || schema_results.table_name || ''')) AS total_size_pretty FROM ' || quote_ident(schema_results.table_schema) || '.' || quote_ident(schema_results.table_name) INTO table_results; RETURN NEXT table_results; END LOOP; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

46. Counting the tables • SELECT * FROM tools.count_schema_rows ('dffax');

47. Writing Advanced Postgres Functions By Lloyd Albin 1/7/2014

48. Sample of Functions • Materialized Views • Single Table Shadow functions. • Lookup User Dependencies • Update sequences for an entire schema