set-returning-functions

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

May 12, 2023 by Tarik

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

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

April 12, 2023 by Tarik

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

Split column into multiple rows in Postgres

February 10, 2023 by Tarik

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 unnest() with element number

December 19, 2022 by Tarik

Postgres 9.4 or later Use WITH ORDINALITY for set-returning functions: When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function’s output. This is most useful in the case of set returning … Read more