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.
Questions tagged [functions]
761 questions
88
votes
2 answers
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue to the column creation one that is solved with…
GuidoS
- 1,047
- 1
- 8
- 7
83
votes
2 answers
How to join a table with a table valued function?
I have a user defined function:
create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
-- blah blah
end
Now I want to join this on another table, like so:
select f.ID,…
Shaul Behr
- 2,963
- 8
- 34
- 42
67
votes
1 answer
Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?
It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans:
If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation…
Paul White
- 94,921
- 30
- 437
- 687
50
votes
1 answer
SQL injection in Postgres functions vs prepared queries
In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?
user4930
43
votes
1 answer
Transform all columns records to lowercase
I'm using PostgreSQL 9.1 and I have a users table with a login column.
login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. How can I do that?
flyer88
- 607
- 2
- 6
- 7
40
votes
1 answer
Postgres function assign query results to multiple variables
I need to assign values to 2 variable as below in Postgres function.
a := select col1 from tbl where ...
b := select col2 from tbl where ...
How can I assign 2 values to 2 variables in one line command?
Like
a,b := select col1,col2 from tbl where…
Xianlin
- 567
- 2
- 6
- 10
39
votes
5 answers
In PostgreSQL, is there a type-safe first() aggregate function?
I'm looking for a first() aggregate function.
Here I found something that almost works:
CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement)
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $1;
$$;
-- And then wrap…
Alexandre Neto
- 577
- 2
- 5
- 11
36
votes
1 answer
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:
create function f1()
returns integer as
$$
declare
event tablename%ROWTYPE;
....
....
begin
FOR event IN
SELECT * FROM tablename WHERE condition
LOOP
…
skumar
- 371
- 1
- 3
- 7
33
votes
1 answer
SQL Server 2019 executes unreachable code
[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.]
Consider the following repro example (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
IF @myYear <> 1990
…
Heinzi
- 3,210
- 2
- 32
- 43
32
votes
3 answers
How to grant permissions on a table-valued function
Am I doing it right...?
I have a function that returns money...
CREATE FUNCTION functionName( @a_principal money, @a_from_date
datetime, @a_to_date datetime, @a_rate float ) RETURNS money AS BEGIN
DECLARE @v_dint money set @v_dint =…
Jack Frost
- 471
- 1
- 5
- 8
32
votes
2 answers
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results.
There are some places where a Scalar UDF is the only option, though.
As an example: when dealing with XML: XQuery can't be used as…
Erik Reasonable Rates Darling
- 45,549
- 14
- 145
- 532
31
votes
1 answer
Self referencing scalar function nesting level exceeded when adding a select
Purpose
When trying to create a test example of a self referencing function, one version fails while another one succeeds.
The only difference being an added SELECT to the function body resulting in a different execution plan for both.
The function…
Randi Vertongen
- 16,593
- 4
- 36
- 64
26
votes
1 answer
How does this syntax work? {fn CurDate()} or {fn Now()} etc
Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand. It appears to be some type of function call.
A sample:
SELECT o.name, o.type_desc,…
Hannah Vernon
- 70,928
- 22
- 177
- 323
26
votes
2 answers
Get query plan for SQL statement nested in a PL/pgSQL function
Is it possible to get the query plan for an SQL statement executed inside a user defined function (UDF)? Like with using EXPLAIN in pgAdmin or psql as client. I see the UDF abstracted away into a single operation F() in pgAdmin.
Currently, I pull…
Hassan Syed
- 437
- 1
- 4
- 9
26
votes
2 answers
Why does LEN() function badly underestimate cardinality in SQL Server 2014?
I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is yielding very poor plans because there are…
Geoff Patterson
- 8,447
- 2
- 28
- 53