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

Assign to NEW by key in a Postgres trigger

In the trigger body, how can I assign a value to NEW by it's field name? That's what I want to do: some_key = "some_column"; NEW[some_key] = 5;
Ben's user avatar
  • 257
3 votes
4 answers
31k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
arthur's user avatar
  • 888
2 votes
1 answer
4k views

COMMIT works in one plpgsql code block, but not in another?

Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
Alex A.'s user avatar
  • 163
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
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
8 votes
1 answer
3k views

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

The manual for Postgres 12 says: The following operations are always parallel restricted. Scans of common table expressions (CTEs). Scans of temporary tables. ... Further down on ...
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
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
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
5 votes
1 answer
35k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
WeDoTDD.com's user avatar
4 votes
1 answer
519 views

SQL Server 2017: How does Interleaved Execution work?

Interleaved Execution is part of a family of features in the 2017 query processor that consists of: Batch Mode Adaptive Joins Interleaved Execution Batch Mode Memory Grant Feedback So how does ...
Erik Darling's user avatar
  • 40.8k
3 votes
1 answer
966 views

How can I find out if a SQL function can be inlined?

In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
Evan Carroll's user avatar
  • 63.2k
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
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
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
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
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
8 votes
2 answers
38k 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
7 votes
2 answers
23k views

Use array of composite type as function parameter and access it

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table. This is my type: ...
Postgresql_enthu's user avatar
7 votes
3 answers
9k views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
Natysiu16's user avatar
  • 245
7 votes
1 answer
2k views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
Bogdan Bogdanov's user avatar
5 votes
1 answer
10k views

How to access the number of rows accumulated by RETURNs in PL/pgSQL

When performing RETURN QUERY ... in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
Chau's user avatar
  • 289
4 votes
1 answer
31k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
alexsmn's user avatar
  • 353
2 votes
1 answer
300 views

Super poor UDF performance on only 1 server, not identical other server

I have the following code that takes 20 times longer on one production box (db35) than another identical box (db34). Both have: Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64) Dec ...
Danno's user avatar
  • 41
2 votes
3 answers
3k views

How to find data type of output in MySQL?

I was reading about the CONCAT() function, and I noticed this: mysql> SELECT CONCAT(14.3); -> '14.3' While running this command in MySQL 8.0 Command Line Client, the output didn't have ...
Random Person's user avatar
1 vote
1 answer
1k views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
Bogdan Bogdanov's user avatar
1 vote
2 answers
14k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
virtual void's user avatar
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
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
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
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
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
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
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
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
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