set-returning-functions

Function to loop through and select data from multiple tables

by

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup() RETURNS TABLE(natural_id text, name text, natural_id_numeric text) LANGUAGE plpgsql AS $func$ DECLARE formal_table text; BEGIN FOR formal_table IN SELECT quote_ident(table_name) FROM information_schema.tables WHERE table_schema=”public” AND table_name LIKE ‘formaltable%’ LOOP RETURN QUERY EXECUTE ‘SELECT t.natural_id, t.name, t.natural_id_numeric FROM internal_idlookup i JOIN public.’ || formal_table || ‘ t USING (natural_id_numeric) WHERE i.internal_id … Read more

PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement

by

How are you executing that function? It works as a select statement. Create a table: public.users create table public.users (id int, firstname varchar, lastname varchar); Insert some records: insert into public.users values (1, ‘aaa’,’bbb’),(2,’ccc’,’ddd’); function: my_function CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer, firstname character varying, lastname character varying) AS $$ DECLARE ids … Read more

Split given string and prepare case statement

by

Clean setup: CREATE TABLE tbl ( given_date date , set_name varchar ); Use a singular term as column name for a single value. The data type is obviously date and not a timestamp. To transform your text parameters into a useful table: SELECT unnest(string_to_array(‘2001-01-01to2001-01-05,2001-01-10to2001-01-15’, ‘,’)) AS date_range , unnest(string_to_array(‘s1,s2’, ‘,’)) AS set_name; “Parallel unnest” is … Read more

Record returned from function has columns concatenated

by

Generally, to decompose rows returned from a function and get individual columns: SELECT * FROM account_servicetier_for_day(20424, ‘2014-08-12’); As for the query: Postgres 9.3 or newer Cleaner with JOIN LATERAL: SELECT ‘2014-08-12’ AS day, 0 AS inbytes, 0 AS outbytes , a.username, a.accountid, a.userid , f.* — but avoid duplicate column names! FROM account_tab a , … Read more

What’s the proper index for querying structures in arrays in Postgres jsonb?

by

First of all, you cannot access JSON array values like that. For a given json value: [{“event_slug”:”test_1″,”start_time”:”2014-10-08″,”end_time”:”2014-10-12″}, {“event_slug”:”test_2″,”start_time”:”2013-06-24″,”end_time”:”2013-07-02″}, {“event_slug”:”test_3″,”start_time”:”2014-03-26″,”end_time”:”2014-03-30″}] A valid test against the first array element would be: WHERE e->0->>’event_slug’ = ‘test_1′ But you probably don’t want to limit your search to the first element of the array. With the jsonb data type in … Read more

Call a set-returning function with an array argument multiple times

by

In Postgres 9.3 or later, it’s typically best to use LEFT JOIN LATERAL … ON true: SELECT sub.dataid, f.* FROM ( SELECT dataid, array_agg(data) AS arr FROM dataset WHERE dataid = something GROUP BY 1 ) sub LEFT JOIN LATERAL foo(sub.arr) f ON true; If the function foo() can return no rows, that’s the safe … Read more

Split column into multiple rows in Postgres

by

In Postgres 9.3+ use a LATERAL join. Minimal form: SELECT token, flag FROM tbl, unnest(string_to_array(subject, ‘ ‘)) token WHERE flag = 2; The comma in the FROM list is (almost) equivalent to CROSS JOIN, LATERAL is automatically assumed for set-returning functions (SRF) in the FROM list. Why “almost”? See: “invalid reference to FROM-clause entry for … Read more

PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”

by

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

Unnest multiple arrays in parallel

by

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

What is the expected behaviour for multiple set-returning functions in SELECT clause?

by

Postgres 10 or newer adds null values for smaller set(s). Demo with generate_series(): SELECT generate_series( 1, 2) AS row2 , generate_series(11, 13) AS row3 , generate_series(21, 24) AS row4; row2 | row3 | row4 —–+——+—– 1 | 11 | 21 2 | 12 | 22 null | 13 | 23 null | null | 24 … Read more