Database functions possibly returning more than one row. Typical examples are series generating functions or unnest(). A.k.a. "table-functions".
Questions tagged [set-returning-functions]
104 questions
100
votes
6 answers
PostgreSQL unnest() with element number
When I have a column with separated values, I can use the unnest() function:
myTable
id | elements
---+------------
1 |ab,cd,efg,hi
2 |jk,lm,no,pq
3 |rstuv,wxyz
select id, unnest(string_to_array(elements, ',')) AS elem
from myTable
id |…
BartekR
- 3,507
- 3
- 22
- 32
58
votes
2 answers
Split column into multiple rows in Postgres
Suppose I have a table like this:
subject | flag
----------------+------
this is a test | 2
subject is of type text, and flag is of type int. I would like to transform this table to something like this in Postgres:
token |…
mgoldwasser
- 11,374
- 11
- 64
- 94
45
votes
2 answers
PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"
As far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work?
create or replace function get_user_by_username(
username varchar(250),
online boolean
) returns setof…
Jeremy Holovacs
- 19,993
- 29
- 99
- 234
21
votes
2 answers
Unnest multiple arrays in parallel
My last question Passing an array to stored to postgres was a bit unclear. Now, to clarify my objective:
I want to create an Postgres stored procedure which will accept two input parameters. One will be a list of some amounts like for instance (100,…
Maki
- 291
- 2
- 4
- 14
19
votes
2 answers
What's the proper index for querying structures in arrays in Postgres jsonb?
I'm experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4:
[{"event_slug":"test_1","start_time":"2014-10-08","end_time":"2014-10-12"},
…
Tony
- 17,359
- 29
- 118
- 188
18
votes
5 answers
Postgresql generate_series of months
I'm trying to generate a series in PostgreSQL with the generate_series function. I need a series of months starting from Jan 2008 until current month + 12 (a year out). I'm using and restricted to PostgreSQL 8.3.14 (so I don't have the timestamp…
Pyrite
- 247
- 1
- 2
- 8
15
votes
2 answers
PostgreSQL: Flattening a relation with an array to emit one row per array entry
Given a table defined as such:
CREATE TABLE test_values(name TEXT, values INTEGER[]);
...and the following values:
| name | values |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |
I'm trying to find a query which will return:
| name…
Charles Duffy
- 235,655
- 34
- 305
- 356
13
votes
1 answer
Function to loop through and select data from multiple tables
I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria.
I could do this with a bunch of UNION queries, but the number of tables I need to search can…
user3813773
- 173
- 1
- 2
- 6
11
votes
1 answer
How to select multiple rows filled with constants in Amazon Redshift?
I have already tried the common PostgreSQL answer, but seems like it doesn't work with Redshift:
SELECT * FROM VALUES (1) AS q (col1);
ERROR: 42883: function values(integer) does not exist
I need this because for some reason I can't use UNION…
Inferno1892
- 133
- 1
- 9
8
votes
1 answer
Use Values from JSONB Array inside a WHERE IN Clause
I have a JSONB object in PostgreSQL:
'{"cars": ["bmw", "mercedes", "pinto"], "user_name": "ed"}'
I am trying to use values from the "cars" array inside it in the WHERE clause of a SELECT:
SELECT car_id FROM cars WHERE car_type IN ('bmw',…
bjones1831
- 247
- 4
- 11
7
votes
3 answers
What is the expected behaviour for multiple set-returning functions in SELECT clause?
I'm trying to get a "cross join" with the result of two set-returning functions, but in some cases I don't get the "cross join", see example
Behaviour 1: When set lenghts are the same, it matches item by item from each set
postgres=# SELECT…
jperelli
- 6,382
- 4
- 43
- 82
7
votes
2 answers
Parallel unnest() and sort order in PostgreSQL
I understand that using
SELECT unnest(ARRAY[5,3,9]) as id
without an ORDER BY clause, the order of the result set is not guaranteed. I could for example get:
id
--
3
5
9
But what about the following request:
SELECT
unnest(ARRAY[5,3,9]) as id,
…
Jerome WAGNER
- 20,021
- 6
- 55
- 73
7
votes
1 answer
NULL emements lost when casting result of unnest()
I stumbled upon very odd behavior with unnest(), when casting after expanding an array.
Introduction
There are three basic syntax variants to use unnest():
1) SELECT unnest('{1,NULL,4}'::int[]) AS i;
2) SELECT i FROM unnest('{2,NULL,4}'::int[]) AS…
Erwin Brandstetter
- 479,275
- 111
- 893
- 1,042
6
votes
2 answers
How to return a table's rowtype plus an additional column from a function?
I have a table defined like so:
create table users (
id serial primary key,
name text,
email text,
);
...and I want to write a function that returns rows of the shape:
(
id integer,
name text,
email text,
some_other_column…
x1f577
- 175
- 6
6
votes
1 answer
Error while using regexp_split_to_table (Amazon Redshift)
I have the same question as this:
Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables
Just that my 'fruits' column is delimited by '|'. When I try:
SELECT
yourTable.ID,
…
Reise45
- 1,023
- 3
- 14
- 22