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