Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
3answers
87 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].[...
3
votes
2answers
304 views

Select into specific array positions with array_agg()?

Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.) For example, I would like to select an item and its stock ...
0
votes
1answer
39 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
40 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
21 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
28 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
55 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
69 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 ...
0
votes
1answer
26 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....
7
votes
1answer
863 views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
1
vote
1answer
61 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
296 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 ...
1
vote
2answers
917 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
52 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 ...
2
votes
1answer
258 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 ...
7
votes
2answers
16k views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
8
votes
2answers
8k views

Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do: I have a table, fields: id SERIAL inet INET ports integer[] id | inet | ports ----+------------+------------ 2 | 1.2.2.1 ...
2
votes
1answer
238 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
63 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 ...
1
vote
1answer
109 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 ...
3
votes
2answers
2k 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
142 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 ...
1
vote
2answers
98 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 ...
-1
votes
1answer
36 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)...
2
votes
1answer
156 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
1k 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
386 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 ...
1
vote
2answers
2k 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 ...
2
votes
1answer
410 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(...
4
votes
1answer
96 views

Is it safe to use set-returning sql functions instead of security-barrier views?

Since 9.2, it's been possible to use the security_barrier attribute to guard against maliciously-chosen functions and operators accessing data 'hidden' behind filters in views (full info in the ...
8
votes
1answer
338 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
864 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 ...
4
votes
2answers
8k views

Filling in missing dates in record set from generate_series()

Consider: with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) ...
6
votes
2answers
10k 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 ...
11
votes
1answer
16k views

Error: set_valued function called in context that cannot accept a set. What is it about?

I use Postgresql 9.1, with ubuntu 12.04. Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
8
votes
1answer
2k 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 ...
1
vote
1answer
29 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
118 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 ...
5
votes
2answers
697 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 ...
3
votes
1answer
2k views

How to use RETURNS TABLE with an existing table in PostgreSQL?

From the PostgreSQL documentation here: There is another way to declare a function as returning a set, which is to use the syntax RETURNS TABLE(columns). ... This notation is specified in recent ...
1
vote
2answers
153 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
2k 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
37 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 ...
1
vote
1answer
820 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 ...
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
1k 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
2k 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
855 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
50 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 ...