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
87 votes
2 answers
137k views

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 ...
GuidoS's user avatar
  • 1,037
81 votes
2 answers
173k views

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 ...
Shaul Behr's user avatar
  • 2,933
63 votes
1 answer
13k views

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 ...
Paul White's user avatar
  • 84.2k
47 votes
1 answer
33k views

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?
MickeyfAgain_BeforeExitOfSO's user avatar
43 votes
1 answer
48k views

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. ...
flyer88's user avatar
  • 607
40 votes
1 answer
81k views

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 ...
Xianlin's user avatar
  • 567
38 votes
5 answers
56k views

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 ...
Alexandre Neto's user avatar
35 votes
1 answer
33k views

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 ...
skumar's user avatar
  • 361
33 votes
1 answer
2k views

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 ...
Heinzi's user avatar
  • 3,145
32 votes
2 answers
4k views

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 ...
Erik Darling's user avatar
  • 40.8k
31 votes
3 answers
109k views

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 ...
Jack Frost's user avatar
31 votes
1 answer
1k views

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 ...
Randi Vertongen's user avatar
26 votes
2 answers
2k views

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 ...
Geoff Patterson's user avatar
26 votes
1 answer
9k views

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 ...
Hannah Vernon's user avatar
  • 70.1k
24 votes
9 answers
9k views

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 ...
MSIS's user avatar
  • 875
24 votes
2 answers
15k views

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 ...
Hassan Syed's user avatar
22 votes
1 answer
18k views

What is the difference between an after update and a before update in PostgreSQL

What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was ...
oriaj's user avatar
  • 355
21 votes
2 answers
60k views

PostgreSQL: Pass table as argument in function

I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example: CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId ...
jlandercy's user avatar
  • 357
20 votes
3 answers
20k views

What does NVL stand for?

What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
newenglander's user avatar
  • 1,055
20 votes
2 answers
48k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
kupa's user avatar
  • 1,707
20 votes
2 answers
46k views

Why scalar valued functions need execute permission rather than select?

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select? meanwhile a table valued functions works just fine with only select permission or ...
BobNoobGuy's user avatar
20 votes
1 answer
1k views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
beeks's user avatar
  • 1,251
19 votes
2 answers
39k views

Disabling Checking of Schema On Function/Stored Procedure Creation

I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
Brian Mains's user avatar
18 votes
1 answer
63k views

Pass an array or record to a function in PostgreSQL?

I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
Worker's user avatar
  • 519
18 votes
3 answers
44k views

Create new function by code if it doesn't exist

I want to create new function by script in my database. The script code is below: IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return; CREATE FUNCTION ...
mehdi lotfi's user avatar
  • 1,195
18 votes
1 answer
9k views

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement: RETURN expression or a block: BEGIN ATOMIC ...
ARX's user avatar
  • 1,419
18 votes
3 answers
33k views

PostgreSQL 11 error: column p.proisagg does not exist

Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error: ERROR: column p.proisagg does not exist LINE 18: ...
Sébastien Clément's user avatar
18 votes
2 answers
53k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
32bitfloat's user avatar
16 votes
5 answers
24k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however ...
Jeremy Holovacs's user avatar
15 votes
2 answers
72k views

Returning values for Stored Procedures in PostgreSQL

I was reading this on PostgreSQL Tutorials: In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to ...
Purav Uday Desai's user avatar
15 votes
4 answers
2k views

PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method: Introduce 2 new columns as C and D2....
Andy's user avatar
  • 593
14 votes
13 answers
10k views

How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

Given two numbers n and m, I want to generate a series of the form 1, 2, ..., (n-1), n, n, (n-1), ... 2, 1 and repeat it m times. For instance, for n = 3 and m = 4, I want a sequence of the ...
joanolo's user avatar
  • 13.4k
14 votes
2 answers
22k views

Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
Sadeq Dousti's user avatar
14 votes
2 answers
562 views

How to implement a Set based algorithm/UDF

I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns. The algorithm is implemented in VBA and does a bunch of math using values from some columns to ...
medwar19's user avatar
  • 169
13 votes
2 answers
42k views

Return a record with PL/pgSQL function - to speed up the query

I have a non-forking game daemon written in Perl, which uses async queries to write player stats into a PostgreSQL 9 database. But when I need to read something from database (like if a player is ...
Alexander Farber's user avatar
13 votes
2 answers
30k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
Alan Cor's user avatar
  • 337
13 votes
1 answer
2k views

How can I force a scalar UDF to be evaluated just once in a query?

I have a query which needs to filter against the result of a scalar UDF. The query must be sent as a single statement (so I can't assign the UDF result to a local variable) and I cannot use a TVF. I ...
Joe Obbish's user avatar
  • 32.2k
13 votes
1 answer
3k views

Can declaring function volatility IMMUTABLE harm performance?

Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. ...
Erwin Brandstetter's user avatar
12 votes
4 answers
23k views

How to drop all of my functions in PostgreSQL?

Right now I have to use query to get the command in text file. Then remove double quote from them. And finally, run that file in psql shell. How can drop all of my functions in PostgreSQL in a single ...
sarit's user avatar
  • 295
12 votes
3 answers
1k views

Emulate user-defined scalar function in a way which doesn't prevent parallelism

I'm trying to see if there's a way to trick SQL Server to use a certain plan for the query. 1. Environment Imagine you have some data which is shared between different processes. So, suppose we have ...
Roman Pekar's user avatar
12 votes
1 answer
19k views

Postgres get function owner

I would like to be able to get a table of function names and function owner names. Currently, this query returns a list of functions for a given schema: SELECT proname FROM pg_catalog....
4x0v7's user avatar
  • 123
12 votes
1 answer
6k views

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?

Is there a difference between a plain SQL function doing a SQL query: create function get_sports() returns setof sport as $body$ select * from sport; $body$ language sql stable; and PLPGSQL ...
Katrin's user avatar
  • 359
12 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
12 votes
3 answers
5k views

Fastest way to count how many dates' ranges cover each date from series

I have a table (in PostgreSQL 9.4) that looks like this: CREATE TABLE dates_ranges (kind int, start_date date, end_date date); INSERT INTO dates_ranges VALUES (1, '2018-01-01', '2018-01-31'), ...
BartekCh's user avatar
  • 231
12 votes
1 answer
1k views

How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

We have a SQL Server database which has a database audit specification which audits all execute actions on the database. CREATE DATABASE AUDIT SPECIFICATION [dbAudit] FOR SERVER AUDIT [servAudit] ADD ...
Mark Iannucci's user avatar
11 votes
8 answers
3k views

Identity Columns or UDF that explicitly generates a unique id?

I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id. I am arguing for the Identity Column. ...
kacalapy's user avatar
  • 2,052
11 votes
2 answers
3k views

Is support for Parallel Scalar UDF a reasonable feature request?

It is fairly well documented that scalar UDF's force an overall serial plan. Running functions in parallel Given a large number of rows coming into a point in the pipeline where a UDF must be ...
crokusek's user avatar
  • 2,120
11 votes
2 answers
15k views

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

I understand the distinction between: Scalar functions Set-Returning-Functions (SRF)s Internal functions Window functions Aggregate functions of all sorts User-Implemented functions (which in ...
Evan Carroll's user avatar
  • 63.2k
11 votes
1 answer
2k views

Estimated versus actual query plan with function calls

I have this query on SQL server, a merge replication query: SELECT DISTINCT b.tablenick, b.rowguid, c.generation, sys.fn_MSgeneration_downloadonly ( c.generation, ...
peter's user avatar
  • 2,187
11 votes
2 answers
3k views

Are there standard SQL functions with side effects?

Do the SQL standards define functions with side-effects? For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like ...
tinlyx's user avatar
  • 3,540

1
2 3 4 5
16