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".
83
questions
1
vote
1answer
73 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 ...
6
votes
1answer
208 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 ...
0
votes
1answer
28 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
1answer
26 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:...
1
vote
2answers
108 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
vote
1answer
39 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 ...
1
vote
1answer
19 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 ...
1
vote
1answer
54 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 ...
3
votes
1answer
59 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 ...
1
vote
1answer
328 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 ...
0
votes
3answers
209 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 ...
1
vote
1answer
40 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', '...
0
votes
1answer
254 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 ...
3
votes
1answer
633 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 ...
0
votes
1answer
107 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}'),
...
0
votes
1answer
75 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 ...
0
votes
1answer
153 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
...
1
vote
1answer
167 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 ...
-3
votes
3answers
282 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].[...
0
votes
1answer
59 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....
1
vote
1answer
161 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
...
4
votes
1answer
1k 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 ...
2
votes
1answer
742 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
1answer
107 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 ...
3
votes
2answers
9k 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,...
1
vote
1answer
2k 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 ...
1
vote
2answers
165 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 ...
2
votes
1answer
81 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 ...
-1
votes
1answer
47 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 ...
0
votes
2answers
24 views
How do I end function when X
so my function looks like this:
create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with
i1(id1) as select insert_here(a1,a2)...
1
vote
2answers
3k views
SQL Server: Pass table name into table valued function as a parameter
All of my tables have those two columns:
[Id] [uniqueidentifier] NOT NULL
[Revision] [bigint] IDENTITY(1, 1) NOT NULL
Every other time I write a query, I want to eliminate some of the rows. The ...
2
votes
1answer
217 views
Efficiency of Scalar UDF vs TVF
I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which ...
1
vote
1answer
2k views
Getting generate_series(date, date) does not exist
When I run commands like this,
SELECT * FROM generate_series('YESTERDAY'::date, 'TODAY'::date);
SELECT * FROM generate_series('YESTERDAY'::timestamp without time zone, 'TODAY'::timestamp without time ...
1
vote
0answers
494 views
Scalar function with while loop to TVF
Hi I have a view which is used in lots of search queries in my application.
The issue is application queries which use this view is is running very slow.I am investigating this and i found out a ...
2
votes
2answers
4k views
Postgres - Return default value if regex match fails
I'd like to attempt a regex match, and return null if it fails.
The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to ...
1
vote
1answer
324 views
Table Valued UDF vs Stored Proc for passing values to a SELECT statement
I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL ...
8
votes
1answer
438 views
Why does a Set Returning Function (SRF) run slower in a FROM clause?
This is a database-internals question. I'm using PostgreSQL 9.5, I'm wondering why Set Returning Functions (SRFs), also known as Table Valued Functions (TVFs) run slower when in a FROM clause, for ...
2
votes
1answer
699 views
aggregating json fields
I have a table that holds JSON data. In a query with a GROUP BY clause, I'd like to get an array of all of the JSON field names in the result set.
I tried a query like this:
SELECT array_agg(...
8
votes
5answers
17k views
How to recompile all stored procedures and table valued functions in SQL Server database?
In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan. I would like to run this on all stored procedures in a database. Also, I would like to run its ...
1
vote
1answer
33 views
Error in function creating query
There is an error in the following function creating query:
CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
RETURN IF(@counter, @counter:=@...
0
votes
2answers
143 views
TSQL - Intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)
I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical ...
2
votes
1answer
312 views
How does cast work with Set Returning Functions (SRF) like generate_series?
I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument ...
9
votes
1answer
4k views
Understanding Set Returning Function (SRF) in the SELECT List
Why is there a difference in behavior between using a Set Returning Function (SRF) in the SELECT list vs using SRF in the FROM clause?
For example, for a simple SRF returning 2 rows:
CREATE OR ...
5
votes
2answers
898 views
T-SQL Daylight Saving lookup table - poorly performing table-valued function
I've created a "Daylight Savings" lookup calendar table for the GMT region. The function I'm using to query the table to return the local datetime from a UTC datetime is performing poorly.
Any help ...
1
vote
2answers
194 views
Why would adding a column to a view corrupted a table-valued function?
I have a TVP myTVP implemented as select val.* from vw_MyView val. The other day, I modified vw_MyView, replacing:
select Foo,Bar from DB with
select Foo, Bash, Bar from DB.
This had a strange side ...
4
votes
1answer
3k views
Should I add set nocount for functions?
I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC.
I have a scalar-valued function that is called from an ...
0
votes
2answers
38 views
Get value of math operations SQL SERVER
I have a problem on this query, how do I use the result of the query contained in CostoUnitario field.
I see better explain, after: ... as CostoUnitario..
I should insert ,CostoUnitario* Quantita
...
2
votes
1answer
1k views
How do you declare a set-returning-function to only be allowed in the FROM clause?
I was just reading this answer by Erwin Brandstetter. Informative as usual, it makes note that (multi-parameter) unnest is only allowed in the FROM clause. I had never tried it in the SELECT clause so ...
23
votes
1answer
3k views
Does SQL Server cache the result of a multi-statement table-valued function?
A multi-statement table-valued function returns its result in a table variable.
Are these results ever reused, or is the function always fully evaluated every time it is called?
3
votes
2answers
2k views
How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)?
Let's assume I have to very simple tables
CREATE TABLE a(id integer PRIMARY KEY,
t timestamp default now(),
sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY,
t ...
