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".

Filter by
Sorted by
Tagged with
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, ...
New Alexandria's user avatar
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: ...
nanocellule's user avatar
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 ...
Leon's user avatar
  • 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 ...
Tim's user avatar
  • 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 ...
dotconnor's user avatar
  • 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&...
RMcLellan's user avatar
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 ...
T. Zack Crawford's user avatar
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 ...
Izlia's user avatar
  • 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 ...
Tom Shelley's user avatar
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 ...
Grymjack's user avatar
  • 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 ...
Jason Dreyzehner's user avatar
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) ...
pietrushka's user avatar
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() ...
Evan Carroll's user avatar
  • 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 (...
Evan Carroll's user avatar
  • 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 ...
Evan Carroll's user avatar
  • 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 ...
Jozef Podlecki's user avatar
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)...
Héctor's user avatar
  • 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 ...
ARX's user avatar
  • 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)
jerin kj's user avatar
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 ...
chans's user avatar
  • 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,...
erg1998's user avatar
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 ...
Robbie's user avatar
  • 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 ...
Oscar's user avatar
  • 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)
Gabriele D'Onufrio's user avatar
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:...
xehpuk's user avatar
  • 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 ...
MattyZDBA's user avatar
  • 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 ...
parchambeau's user avatar
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 ...
parchambeau's user avatar
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 ...
parchambeau's user avatar
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 ...
Marcin's user avatar
  • 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 ...
soandos's user avatar
  • 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 ...
Ivan Sidaruk's user avatar
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', '...
Manngo's user avatar
  • 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 ...
Kulstad's user avatar
  • 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 ...
topcat's user avatar
  • 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}'), ...
S-Man's user avatar
  • 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 ...
mdahlman's user avatar
  • 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 ...
Roberto's user avatar
  • 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 ...
Kipst's user avatar
  • 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].[...
tarun's user avatar
  • 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....
Radioleao's user avatar
  • 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 ...
Jane.L's user avatar
  • 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 ...
sunnyrjuneja's user avatar
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 ...
Christophe Loeys's user avatar
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 ...
Jim Clouse's user avatar
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,...
jon's user avatar
  • 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 ...
Tim's user avatar
  • 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 ...
Ivan Voras's user avatar
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 ...
StackOverthrow's user avatar
-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 ...
user avatar