Return selected columns CREATE OR REPLACE FUNCTION get_user_by_username(_username text , _online bool DEFAULT false) RETURNS TABLE ( user_id int , user_name varchar , last_activity timestamptz ) LANGUAGE plpgsql AS $func$ BEGIN IF _online THEN RETURN QUERY UPDATE users u SET last_activity = current_timestamp — ts with time zone WHERE u.user_name = _username RETURNING u.user_id , … Read more
This works: CREATE OR REPLACE FUNCTION avg_purchases(last_names text[] = ‘{}’) RETURNS TABLE(last_name text, avg_purchase_size float8) LANGUAGE sql AS $func$ SELECT last_name, avg(purchase_size)::float8 FROM purchases WHERE last_name = ANY($1) GROUP BY last_name $func$; Call: SELECT * FROM avg_purchases(‘{foo,Bar,baz,”}weird_name”$$”}’); Or (example with dollar-quoting): SELECT * FROM avg_purchases($x${foo,Bar,baz,”}weird_name’$$”}$x$); How to quote string literals: Insert text with single quotes … Read more
Identifying non-consecutive values is always a bit tricky and involves several nested sub-queries (at least I cannot come up with a better solution). The first step is to identify non-consecutive values for the year: Step 1) Identify non-consecutive values select company, profession, year, case when row_number() over (partition by company, profession order by year) = … Read more
VARIADIC Like @mu provided, VARIADIC is your friend. One more important detail: You can also call a function using a VARIADIC parameter with an array type directly. Add the key word VARIADIC in the function call: SELECT * FROM f_test(VARIADIC ‘{1, 2, 3}’::int[]); is equivalent to: SELECT * FROM f_test(1, 2, 3); Other advice In … Read more
To address the question at the top: Assuming standard_conforming_strings = on, like it’s default since Postgres 9.1. Regular expression escape function Let’s start with a complete list of characters with special meaning in regular expression patterns: !$()*+.:<=>?[\]^{|}- Wrapped in a bracket expression most of them lose their special meaning – with a few exceptions: – … Read more
You must defend against SQL injection whenever you turn user input into code. That includes table and column names coming from system catalogs or from direct user input alike. This way you also prevent trivial exceptions with non-standard identifiers. There are basically three built-in methods: 1. format() 1st query, sanitized: CREATE OR REPLACE FUNCTION foo(_t … Read more
You will love this new feature of Postgres 9.4: unnest(anyarray, anyarray [, …]) unnest() with the much anticipated (at least by me) capability to unnest multiple arrays in parallel cleanly. The manual: expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause; It’s a special … Read more
FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here’s the script: CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$ DECLARE statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username AND schemaname=”public”; BEGIN FOR stmt IN statements LOOP EXECUTE ‘TRUNCATE TABLE ‘ || quote_ident(stmt.tablename) || ‘ CASCADE;’; END LOOP; END; $$ LANGUAGE … Read more
Use RETURN QUERY: CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int) RETURNS TABLE (txt text — also visible as OUT param in function body , cnt bigint , ratio bigint) LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY SELECT t.txt , count(*) AS cnt — column alias only visible in this query , (count(*) * 100) / _max_tokens … Read more
In PL/PgSQL parser, assignment operator is defined as assign_operator : ‘=’ | COLON_EQUALS ; This is a legacy feature, present in source code since 1998, when it was introduced – as we can see in the PostgreSQL Git repo. Starting from version 9.4 it is oficially documented. This idiosyncrasy – of having two operators for … Read more