We’re rewarding the question askers & reputations are being recalculated! Read more.

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
2
votes
1answer
165 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 ...
2
votes
1answer
5k views

Selecting only overlapping elements from array of ranges

The table I need to search contains an array of numrange values illustrated by: CREATE TABLE data ( sensor varchar(25), ranges numrange[] ); INSERT INTO data VALUES ('sensor0','{"[872985609.0,...
3
votes
2answers
2k views

Split up interval into year slices

Being a novice Postgres user, I have a table ad in my PostgreSQL 9.5 (x64) database with 87 rows. In addition to other columns, it has two columns 'start' and 'end' having date-time duration range ...
6
votes
1answer
3k views

Function that returns table with an additional column

I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)? For example, ...
2
votes
1answer
1k views

How to insert a record returned by a function into a table

I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record. CREATE FUNCTION my_func( IN id bigint, OUT foo bigint, OUT bar double precision ...
18
votes
1answer
834 views

Multi-statement TVF vs Inline TVF Performance

Comparing some of the answers on the Palindrome question(10k+ users only, since I've deleted the answer), I'm getting confusing results. I proposed a multi-statement, schema-bound TVF which I thought ...
6
votes
5answers
5k views

UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy: Exact match ...
2
votes
1answer
802 views

Incorrect statistics are used on index seek

I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in ...
2
votes
1answer
111 views

Grouping data by year based on two timestamp columns

My data table has the following columns: id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP I want to product a report of the count of each name (which can appear several times in the table)...
-1
votes
1answer
883 views

Impact of WHERE Clause on Table-Valued Function and query performance

I have taken over a legacy application that has had many performance issues. One I found is on a table valued function. The TVF runs fine as a basic SELECT. However, it is incredibly slow when it has ...
4
votes
2answers
4k views

Cross-product between table column and input values

I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values. Something along the lines of: WITH {1,2} as Input Select * From ...
2
votes
2answers
232 views

Generate a sequence of timestamps but skip given periods

I have a table things_to_do in PostgreSQL. For example: thing_id --------- 1 2 3 4 5 6 And I want to add a timestamp to every row: SELECT thing_id, '2016-02-10 19:57:00'::timestamp ...
0
votes
1answer
496 views

How to write function in sql which accept table as input and return result back as table?

I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in ...
3
votes
1answer
433 views

Need for recursive lookup, stuck in query design

In PostgreSQL 9.4, here is my table: with r(pk, userid,partneruserid) as (values (1,1,2), (2,1,3), (3,1,6), (4,2,5), (5,2,6), (6,3,1), (7,4,1), (8,5,3), (9,6,2), (10,6,3) ) select * from r; In a ...
1
vote
1answer
554 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
5
votes
1answer
12k views

Call function where argument is a (sub)select statement

My function takes an int4 as argument and returns a table: SELECT * FROM test_function(545421); -- works fine SELECT * FROM test_function(SELECT customerid FROM tableX ...
0
votes
0answers
144 views

How can I replace the cursor in the below query?

I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the ...
2
votes
2answers
8k views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
2
votes
1answer
781 views

RETURN NEXT in Postgres Function

I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data. I was originally going to return an array, but I found a resource that explains the "...
3
votes
1answer
4k views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
3
votes
1answer
3k views

returning set of records in stored procedure

I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures: create or ...