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".
78
questions
1
vote
1answer
35 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
16 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
35 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
49 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
100 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
100 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
35 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
141 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
244 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
64 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
43 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
82 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
127 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
181 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
57 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
123 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
752 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
529 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
100 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
6k 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
803 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
137 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
71 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
45 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
2k 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
195 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
453 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
3k 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
255 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
392 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
567 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(...
7
votes
5answers
14k 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
139 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
300 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 ...
8
votes
1answer
3k 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
822 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
184 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 ...
3
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 ...
22
votes
1answer
2k 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 ...
1
vote
1answer
1k views
Can a TVF use multiple CTE statements internally?
I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs.
The intended function defines an input parameter(@Param) and and ...
1
vote
1answer
4k views
jsonb_each_text(jsonb) returns jsonb, not text
According to docs , jsonb_each_text(jsonb) returns
setof key text, value text
but if you pg_typeof(value) on result it shows jsonb, Eg:
SELECT *, pg_typeof(key) AS type_key, pg_typeof(value) AS ...
3
votes
0answers
1k views
Does PostgreSQL support `unnest` as a column?
Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column,
SELECT
unnest(array_agg(id)) AS id
FROM foo
GROUP BY CUBE (a,b,c,d)
HAVING count(*) > 1
If ...
0
votes
1answer
54 views
Check for duplicate data from called functions
I'm new to SQL and am not sure how to go about getting the result set I am after. Below is the closest I have gotten to my desired result set although I am seeing duplicate data.
I have a stored ...
2
votes
1answer
171 views
Audit use of CLR Table-valued function
I have written some CLR table-valued functions that invoke a web service and I would like to audit their use. Does anyone know of any way this can be achieved in the CLR code itself? I would be nice ...
