Questions tagged [functions]

A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.

Filter by
Sorted by
Tagged with
11 votes
1 answer
11k views

How to use an array as argument to a VARIADIC function in PostgreSQL?

I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module. I would like this to be a simple wrapper around the built-in function, the only difference being ...
Jake Feasel's user avatar
11 votes
1 answer
1k views

After upgrading to SQL Server 2019, function throws "insufficient memory" error

I have moved an SQL Server 2012 Database (8GB in size) to a newly setup SQL Server 2019 virtual machine with the same memory and CPU configuration and changed the compatibility level to SQL Server ...
dsungaro's user avatar
  • 113
11 votes
1 answer
11k views

Postgres : Truncate if exists in psql function with parameter

I am trying to get a psql function that would truncate a given table name if it exists. I've been trying multiple functions, but none of them worked so far. Here is the code : CREATE OR REPLACE ...
Standaa - Remember Monica's user avatar
11 votes
3 answers
314 views

Theil-Sen estimator function in T-SQL

Does anyone have a Theil-Sen regression function written in T-SQL? I found one written in Perl, but I'm not able to recode it into SQL.
kafe's user avatar
  • 331
11 votes
2 answers
829 views

Why Does SQL Server Track Time Accurately For Some Multi-Statement Table-Valued Function Query Plans, And Not Others?

Setup For this demo, I'm using the 2013 version of the Stack Overflow database, and SQL Server 2022 CTP2, but it is valid going back through SQL Server 2017 which is as far back as I care to check. ...
Erik Darling's user avatar
  • 40.6k
11 votes
1 answer
185 views

Inline Scalar UDF Under RCSI - Can Results Differ

SQL Server 2019 introduces Scalar UDF inlining, aka "Froid." This ".. embeds [scalar UDFs] in the calling SQL query." Previously, scalar UDFs ran under their own execution context, separate from the ...
Michael Green's user avatar
10 votes
5 answers
39k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
OvetS's user avatar
  • 103
10 votes
3 answers
1k views

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
user9516827's user avatar
  • 1,315
10 votes
4 answers
16k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
Matt Fenwick's user avatar
  • 1,318
10 votes
2 answers
12k views

Why is the schema prefix (dbo) mandatory when we call a function?

When user is mapped with default schema (dbo) and we can select all tables under [dbo] without prefixing the schema. We can execute stored procedures without prefix if it's under the default schema. ...
Rajesh Ranjan's user avatar
10 votes
1 answer
16k views

When to use stored procedure / user-defined function?

I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database. Is there any general advice to ...
DANG Fan's user avatar
  • 211
10 votes
3 answers
11k views

CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

I am facing the following error: ERROR: functions in index expression must be marked IMMUTABLE When trying to create index like this: CREATE INDEX full_phone_number ON orders_clientphone (concat(...
andilabs's user avatar
  • 627
10 votes
1 answer
6k views

PL/pgSQL issues when function used twice (caching problem?)

I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem. I have this function: CREATE FUNCTION sp_connect(mail character varying, passwd character ...
Eric Ly's user avatar
  • 1,212
10 votes
2 answers
20k views

Call multiple functions from trigger?

I run PostgreSQL-9.2.4 Is it possible to call 2 functions from a trigger? Let's say I have two functions for two different tables to be executed if following trigger fires: Trigger: CREATE TRIGGER ...
VL-80's user avatar
  • 203
10 votes
2 answers
9k views

A function to check if a column allows NULL

Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise? I would be something like: UPDATE mytable ...
Wolf480pl's user avatar
  • 103
10 votes
1 answer
36k views

Accessing the OLD/NEW table value in a trigger function in plain SQL

I am trying to build a trigger function to automatically update parent order values any time any of it's items changes. This is what I have created: CREATE OR REPLACE FUNCTION update_totals() ...
eComEvo's user avatar
  • 379
10 votes
2 answers
16k views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
Hans's user avatar
  • 239
10 votes
1 answer
521 views

function to receive a character input and return date format (with incorrect input)

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect ...
Pantea's user avatar
  • 1,412
10 votes
3 answers
38k views

Is it possible to define a function within a stored procedure?

I have a stored procedure: create proc sp_MyProc(@calcType tinyint) as begin -- some stuff collating data into #MyTempTable if (@calcType = 1) -- sum select A, B, C, CalcField = sum(...
Shaul Behr's user avatar
  • 2,923
10 votes
1 answer
2k views

Scalar function used in calculated column, what's the cleanest way to update the function?

I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the ...
robertc's user avatar
  • 317
9 votes
4 answers
7k views

MySQL possibility to create global routines (stored procedures and/or functions)

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database. When I tried to create a routine from console (without prior ...
bakytn's user avatar
  • 193
9 votes
3 answers
5k views

Performance of Inline-TVF vs. Views

I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
FistOfFury's user avatar
9 votes
3 answers
11k views

How to pass a table type with an array field to a function in postgresql

i have a table called book CREATE TABLE book ( id smallint NOT NULL DEFAULT 0, bname text, btype text, bprices numeric(11,2)[], CONSTRAINT key PRIMARY KEY (id ) ) and a ...
indago's user avatar
  • 525
9 votes
2 answers
4k views

User Defined Function (UDF) in master database

I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database ...
IT researcher's user avatar
9 votes
1 answer
11k views

Calculating Average Excluding Outliers

I'm looking for a SQL Server function that will let me take an average, excluding outliers. Basically something where: 1, 2, 1, 2, 7 Would average out to 1.5. I'm going to implement this in a ...
John's user avatar
  • 190
9 votes
1 answer
7k views

Find which functions are used

I have a legacy PostgreSQL database schema with hundreds of functions. I know that some of them aren't used anymore, and I want to delete them. What is the easiest way to find which procedures are ...
Andremoniy's user avatar
9 votes
1 answer
814 views

Does SentryOne Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan to make the Query Plan include the UDF. When ...
Gabe's user avatar
  • 1,334
9 votes
1 answer
4k views

Is it Possible to Overload SQL Server Functions?

Is it possible to overload a sql server function? Either a scalar, like ltrim, or an aggregate function, like count? Even if this was a really, really, bad idea. Is it possible? Somewhat of a ...
Anthony Genovese's user avatar
9 votes
1 answer
17k views

Use NOLOCK hint when calling table valued function

Suppose the following function: CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr My actual function will select actual data from many different tables. I (mostly) understand ...
Jeroen's user avatar
  • 879
9 votes
1 answer
11k views

Why do string functions on SQL based database servers start at position 1 instead of 0? [closed]

This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the ...
VKK's user avatar
  • 203
9 votes
1 answer
580 views

Apparently, my CLR assembly function is causing deadlocks?

Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL ...
Russ Suter's user avatar
8 votes
3 answers
103k views

How to Convert Data to Proper Case In SQL Server

SQL Server contains system functions for viewing / updating string data to both uppercase and lowercase but not proper case. There are multiple reasons for desiring this operation takes place in SQL ...
SQL_Deadwood's user avatar
8 votes
1 answer
805 views

Why won't this UDF work in this check constraint?

I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel ...
agerber85's user avatar
  • 111
8 votes
3 answers
3k views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
David Mathis's user avatar
8 votes
4 answers
10k views

Most efficient way to call same Table-Valued Function on multiple columns in a Query

I am trying to tune a query where the same table-valued function (TVF) is called on 20 columns. The first thing I did was convert the scalar function into an inline table-valued function. Is using ...
Mazhar's user avatar
  • 793
8 votes
2 answers
37k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
boobiq's user avatar
  • 807
8 votes
2 answers
3k views

list of PostgreSQL trusted languages?

I am trying to find a list of PostgreSQL trusted procedural languages (PL) as I often need to use a PL in a non-superuser environment. But I couldn't find a definitive answer. For example, the ...
tinlyx's user avatar
  • 3,510
8 votes
1 answer
577 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 ...
Evan Carroll's user avatar
  • 62.9k
8 votes
2 answers
362 views

Is a dropped (or altered) function still available inside already opened transactions?

I found PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions? but there are no answers and isn't exactly the same as my question (though very similar). ...
Wildcard's user avatar
  • 588
8 votes
1 answer
252 views

Disallow new UDFs that contain a cursor

Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
William Meitzen's user avatar
8 votes
2 answers
2k views

Why do we have undocumented and unsupported functions in SQL Server?

After reading an article, dated 2012, about the undocumented functions fn_dblog and fn_dump_dblog I was wondering why they are still undocumented and unsupported if they have been around for a long ...
Ronaldo's user avatar
  • 5,079
8 votes
1 answer
10k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for example)...
arthur's user avatar
  • 878
8 votes
1 answer
9k views

Return ResultSet with column names from functions

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
gjain's user avatar
  • 183
8 votes
3 answers
10k views

How do I save functions to individual files in PostgreSQL?

I maintain a legacy application that uses a PostgreSQL database. The application is heavily dependent on stored procedures (aka functions). I want to save these functions to files named after the ...
Fernando's user avatar
  • 221
8 votes
1 answer
31k views

PostgreSQL: Auto update updated_at with custom column [duplicate]

Before, I would join multiple tables into one and return result, but in many tables I had same column names. That's why I decided to to prefix column names with table name. But that broke the trigger ...
SeemsIndie's user avatar
8 votes
3 answers
18k views

Function extract() from date returns double precision value

Why does the Postgres function extract() return double precision from date value? Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported ...
simar's user avatar
  • 203
8 votes
1 answer
505 views

Schema-binding with recursive scalar UDF

TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes ...
Charlieface's user avatar
  • 12.7k
8 votes
2 answers
33k views

SQL server create temporary function as a CTE

I need to create a temp function, for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables). Is there any way that I could use a CTE or a #temp query ...
heyNow's user avatar
  • 275
8 votes
3 answers
2k views

After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL ...
PhilipD's user avatar
  • 189
8 votes
1 answer
14k views

What is included in ALL permissions for functions in PostgreSQL?

I am trying to find references to what the difference between EXECUTE and ALL is in: GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }... but all I can find is what ...
dw8547's user avatar
  • 927

1
2
3 4 5
16