Questions tagged [set-returning-functions]
Functions possibly returning more than one row. Typical examples are series generating functions or unnest(). A.k.a. "table-functions".
103
questions
3
votes
2
answers
300
views
Function returns one row, but the query it wraps returns many
A very odd situation, where a query returns 1380 items, but when wrapped in a function, then same function returns a single row
I've tried deleting and recreating the function.
When called like this, ...
- 137
0
votes
1
answer
42
views
Is it possible to sort rows by values from generate_series()?
I multiply result rows of a SELECT with CROSS JOIN generate_series(1, max). My query is like:
select id, name
from person
CROSS JOIN generate_series(1, 4)
where <condition>;
I have this result:
...
0
votes
2
answers
52
views
How to correctly select multi-columns from a return of a function in Postgresql?
I have hundreds of tables with same structure in a Postgresql-11 db, and I need to perform a
same statistics on each of them.
Since the real statistics logic is very complex, in order to simplify my ...
- 285
4
votes
2
answers
614
views
Are table-valued functions deterministic with regard to insertion order?
Is insertion order deterministic in a TABLE-valued function which instantiates the table and populates it with successive inserts, assuming no ORDER BY clause is used?
I needed to take a look at the ...
- 435
1
vote
1
answer
388
views
Unnest array of rows with a tagged column
I'm trying to create a query that will return data in this format:
id
time
value
1
1
2.5
1
2
3.5
1
3
6.4
2
1
8.3
2
2
8.5
I'm using Timescaledb and wish to use one of their downsampling ...
- 135
4
votes
2
answers
436
views
Pair top-level key with nested value from jsonb document
Given the following data:
create table datas (id int, data jsonb);
insert into datas (id, data)
values
(1, '{"key1": {"foo": "bar1","some":"thing1&...
- 43
0
votes
0
answers
400
views
TSQL (SQL Server) Table-valued function whose return-form depends on parameters
I have a table who has an arbitrary number of bit columns which can change over time.
Key
Bit1
Bit2
Bit3
'something1'
1
0
0
'something2'
1
1
0
'something3'
0
0
1
What I am trying to do is set up ...
- 101
0
votes
1
answer
1k
views
Return type mismatch in function declared
I am working in supabase, where I want to make a function with the help of SQL queries to obtain the last record of a user.
As it is a query that would not look entirely good using the traditional ...
- 101
0
votes
1
answer
34
views
Multiple Query Insert and Return
I have the following code which creates a Mapbox Vector Tile(MVT). At the moment it creates, stores and returns to the user. I would like however to create multiple in one query however i'm getting ...
0
votes
1
answer
800
views
Sqlite fails when performing an 'insert' with a 'returning' value clause
I am having problems with SQLite? I didn't have this problem when using the same tables on PostgreSQL. When the table is empty and you use the following SQL statement it throws an error.
INSERT INTO ...
- 103
0
votes
1
answer
267
views
Filter rows using a probabilistic filter (bloom filter or cuckoo filter) in Postgres
I have a large table (~1B rows) with a btree index on a single bytea column (~20GB, but fits in memory).
I'd like to send a query from the client with a probabilistic filter (e.g. a bloom filter or a ...
- 155
2
votes
1
answer
2k
views
Unnest array of arrays
In my parameterized query I have to unnest arrays with 1 dimension and 2 dimensions:
$1: ARRAY['id1', 'id2']
$2: ARRAY[ARRAY['tag1'], ARRAY['tag2']]
I tried this query:
INSERT INTO table (id, tags)
...
- 21
2
votes
1
answer
3k
views
What is the difference between RETURNS `SETOF integer` and RETURNS TABLE(name int)`?
Given the two definitions below, is there any difference?
CREATE FUNCTION foo(OUT foobar int4)
RETURNS SETOF int4
AS 'MODULE_PATHNAME', 'foo'
LANGUAGE C STRICT VOLATILE;
And,
CREATE FUNCTION foo()
...
- 61.4k
0
votes
1
answer
215
views
In what circumstance is Materialize_Random set in allowedMode?
In PostgreSQL there is a field called allowedMode called SFRM_Materialize_Random
The Tuplestore must be created with randomAccess = true if
SFRM_Materialize_Random is set in allowedModes, but it can (...
- 61.4k
0
votes
1
answer
193
views
C Extension: Is it faster to return a set with ValuePerCall or Materialize mode?
PostgreSQL documents it's Set Returning Functions with C extensions like this,
C-language functions have two options for returning sets (multiple rows).
In one method, called ValuePerCall mode, a ...
- 61.4k
0
votes
1
answer
186
views
SQL Profiler shows multiple calls for one TVF
There is one table valued function in SQL Profiler I have noticed which seems to "call itself multiple times".
The definition doesnt do any odd thing either, it is a query with few JOINs and ...
- 187
1
vote
1
answer
3k
views
SELECT rows from table one by one, comparing one column values with values in an array
I have an array of timestamps in a PLPGSQL code block: timestamp_array.
For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table)...
- 277
0
votes
1
answer
3k
views
Difference between unnest() in SELECT vs unnest() in FROM on Postgres
I want the lexemes of a tsvector array placed in a column, one per row. I can first unnest the array and then the resulting tsvectors with:
SELECT (unnest(unnest(my_array))).lexeme
FROM my_table
...
- 1,379
3
votes
2
answers
337
views
Generate series 0.01 to 0.99
Is there any simple method to get a series:
0.01
0.02
0.03
...
...
...
0.97
0.98
0.99
like series generation from 1 to 99
SELECT * FROM generate_series(1, 99)
- 33
0
votes
1
answer
197
views
load text file into db using temp variable as place holder for complex column value
Assume I have a text file with tab terminated fields as below. Where value in row m, column n as vmn. Vmn is a string which could wrapped in double quota. Generally this string variable could have ...
- 1
-3
votes
1
answer
431
views
SQL Query to Return Rows that don't exist as blank [duplicate]
I have a database of information of a possible 1-100 rows. The issue is some of the numbers don't exist.
When I run a query the following results are retuned:
Unit,Location,Name,Description
1,1,a,...
- 1
2
votes
1
answer
391
views
Graceful Test for Linked Server Availability Inside a TVF or View
I’ve got a mission critical table valued function that needs to return results from several linked servers simultaneously. If any of those servers are down, it still needs to return results from the ...
- 61
6
votes
1
answer
698
views
How to return several arrays of doubles as a table in C# SQLCLR function
What I have is a CLR SqlFunction that produces 3 arrays of doubles. I would like this function to return something appropriate so that the FillRowMethod can output it for me as a table in T-SQL. It ...
- 249
0
votes
1
answer
251
views
replace user defined functions (UDF) and tabled value functions(TVF)
Are there techniques to replace
UDF
tabled value functions
with something else?
or techniques to make them acceptable? (fast, multithreading and with good estimations)
0
votes
1
answer
239
views
Give alias / column definition list to ROWS FROM()
This obviously doesn't work:
SELECT regexp_matches[1], regexp_matches[1]
FROM ROWS FROM (
regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
);
Error:...
- 277
1
vote
2
answers
764
views
Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?
I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to ...
- 1,913
1
vote
1
answer
114
views
How do I optimize my transaction level running balances cartesian join?
This is a continuation of an question found here:
Generate multiple running totals with GROUP BY day
This is a continuation of a previous question, where table definition and sample data can be ...
- 139
1
vote
1
answer
302
views
Return previous running total when value is null in a time series
This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).
All of this is being done on a PostgreSQL ...
- 139
1
vote
1
answer
441
views
Generate multiple running totals with GROUP BY day
I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
- 139
3
votes
1
answer
106
views
generating all substrings (n-grams) for bit-strings
I followed SELECT all substrings (n-grams) of length n? to get a function for generating all n-grams for text types. Works great. I figured out how to cast my bit-strings to text, and the function ...
- 131
2
votes
1
answer
2k
views
Extract and combine multiple values from a jsonb column
I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null.
fiddle
Setting up schema:
create table jsonb_test (test ...
- 157
0
votes
3
answers
1k
views
PostgreSQL: Is it possible to get a different function result type (i.e. TABLE) in stored function based on input?
The question is as follow, I wanted to get some flexibility in my example function, so based on input arguments to function I wanted to return different table as an output result.
So is it possible ...
- 123
1
vote
1
answer
255
views
Table-valued Data in Common Table Expression
Most other databases allow you to generate table values using VALUES. As far as I can tell, SQL Server lets you do this with subqueries:
SELECT * FROM (
VALUES
('Cornelius','Eversoe', '...
- 2,709
0
votes
1
answer
793
views
Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records
I am loading hundreds of order files (positional flat files), via SSIS, into staging tables, and ultimately into production tables. As part of the staging table population process, I would like to ...
- 55
3
votes
1
answer
3k
views
Fill missing dates within groups
I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
- 187
0
votes
1
answer
5k
views
Understanding joined set-returning functions in the FROM list
I am using a construct like this very often:
SELECT *
FROM
my_table,
my_srf(my_column)
for example here:
CREATE TABLE my_table (
my_col text[]
);
INSERT INTO my_table VALUES
('{a,b}'),
...
- 169
0
votes
1
answer
394
views
Reusing literal string in different parts of query
I created this PostgreSQL query while troubleshooting a problem that turned out to be due to non-printing characters in my strings:
with cte as ( select 'The character between the xes is evil: xx' as ...
- 347
1
vote
1
answer
1k
views
Refactor SQL to left join on TVF requiring input variable
I would like to join two tables and a table-valued function. The TVF take a date as input variable.
The expected output is:
Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible
...
- 119
1
vote
1
answer
600
views
Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?
I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists).
An example ...
- 13
-3
votes
3
answers
2k
views
if statement in a query [closed]
I am getting an error while running this code:
Incorrect syntax near the keyword 'IF'
I need the output through function only with table return .
CREATE FUNCTION [dbo].[...
- 1
0
votes
1
answer
105
views
Postgresql table function: get where condition
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.
Here, there is a very simple example:
create or replace function public....
- 125
1
vote
1
answer
2k
views
Surprising results from assigning multiple variables in FOR loop
CREATE OR REPLACE FUNCTION my_function(input jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS -- language declaration required
$func$
DECLARE
_key text;
_value text;
BEGIN
FOR _key, _value IN
...
- 113
4
votes
1
answer
2k
views
Slow left join lateral in subquery
I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
- 143
2
votes
1
answer
2k
views
'Ungroup' data based on column
I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has ...
1
vote
1
answer
207
views
Multi-statement Table Valued Function causing massive PAGELATCH_EX waits
We are getting insane PAGELATCH_EX waits because of a Multi-Statement TVF. I've found that the temp table created by the function is on tempdb file #1 and doesn’t ever change (always '#A465FC0D'). It ...
- 111
5
votes
2
answers
19k
views
jsonb_array_elements() in the select seems to turn a left join into an inner join
PostgreSQL 9.6.
create table jon.vins (vin citext primary key);
insert into jon.vins values
('3GNAXUEV1KL221776'),
('3GNAXHEV2KS548975');
CREATE TABLE jon.describe_vehicle (
vin citext primary key,...
- 53
1
vote
1
answer
5k
views
PostgreSQL - Return type and EXECUTE QUERY
My function returns a setof composite type, however the RETURN EXECUTE seems to return some other unknown type:
create type core.search_result as (
user_id uuid,
country core.country,
ethnicity ...
- 113
1
vote
2
answers
319
views
How to fan out rows with a specified range of values?
I have a table which essentially contains 3 columns: ID, FIRST, LAST. They're all integers.
I'm wondering if there's a way to use generate_series() to get a query result where for each row in the ...
- 352
2
votes
1
answer
112
views
Query involving TVF very slow on SQL Server 2008
I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
- 121
-1
votes
1
answer
66
views
How to acquire a table result set from "Table - valued function" when the result set has variable structures?
I need to write a Table - valued function from which I can acquire a table result set with different structure each time.
another thing is that I need to declare variables inside my function .
I've ...
user157749