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.

726 questions
81
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
  • 967
  • 1
  • 6
  • 7
75
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,863
  • 8
  • 30
  • 41
59
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
  • 78,233
  • 28
  • 392
  • 615
44
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?
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
39
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
  • 557
  • 2
  • 6
  • 10
34
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,145
  • 2
  • 28
  • 41
32
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
  • 497
  • 2
  • 5
  • 10
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,198
  • 4
  • 32
  • 61
31
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 Darling
  • 36,388
  • 14
  • 127
  • 357
29
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
  • 441
  • 1
  • 5
  • 8
27
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
  • 281
  • 1
  • 3
  • 7
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
  • 68,431
  • 22
  • 166
  • 303
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…
24
votes
9 answers

Test if a string is a palindrome using T-SQL

I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an error message. I am looking for different…
MSIS
  • 875
  • 9
  • 19
1
2 3
48 49