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.
752
questions
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 ...
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 ...
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 ...
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.
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.
...
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 ...
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 ...
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 ...
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 -- ...
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.
...
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 ...
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(...
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 ...
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 ...
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
...
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() ...
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
$$
...
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 ...
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(...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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).
...
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?
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 ...
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)...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...