45
votes
Accepted
In PostgreSQL, is there a type-safe first() aggregate function?
DISTINCT ON()
Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)
SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of ...
- 64.2k
41
votes
Accepted
SQL Server 2019 executes unreachable code
This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for ...
- 85.9k
36
votes
Accepted
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
Yes if you:
are running SQL Server 2014 or later; and
are able to run the query with trace flag 176 active; and
the computed column is PERSISTED
Specifically, at least the following versions are ...
- 88.3k
31
votes
Accepted
Self referencing scalar function nesting level exceeded when adding a select
This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function.
To explain, we need to note two things up front:
SQL Server cannot ...
- 88.3k
27
votes
Accepted
What is the difference between an after update and a before update in PostgreSQL
There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless.
In a BEFORE trigger, the trigger is executed BEFORE the DML statement is ...
- 3,398
25
votes
Accepted
What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?
It's the new (in Postgres 14) syntax variant for SQL-standard functions.
The release notes:
Allow SQL-language functions and procedures to use SQL-standard function bodies (Peter Eisentraut)
...
- 180k
21
votes
Accepted
Postgres get function owner
There is a simple and a complicated way.
The complicated way is to join with pg_roles and get the user name from there.
But since PostgreSQL hackers don't want to type more than necessary, they came ...
- 54.9k
18
votes
Are there standard SQL functions with side effects?
You have a few different questions in here.
Q: What are ANSI standard SQL functions?
ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that'...
- 43.1k
18
votes
Accepted
How can I force a scalar UDF to be evaluated just once in a query?
Ultimately, it is not possible to force SQL Server to evaluate a scalar UDF just once in a query. However, there are some steps which can be taken to encourage it. With testing I believe that you can ...
- 32.6k
17
votes
Accepted
How can I eliminate this scalar function or make it faster?
Rewrite the scalar function as an in-line table-valued function, or upgrade to SQL Server 2019, which will automatically perform that translation for you.
Machine Translation
The following is the ...
- 88.3k
17
votes
Why won't this UDF work in this check constraint?
The error is because the expression in a CHECK constraint must result in a boolean value (though there is no BOOLEAN datatype in SQL Server). Yours returns a bit value.
You can technically solve this ...
- 98.5k
17
votes
Accepted
Are views logically redundant?
Both views and inline TVFs are updatable, and can be used interchangeably almost everywhere.
Specifically for SQL Server, the following features are supported by views that are not available for ...
- 13.9k
16
votes
In PostgreSQL, is there a type-safe first() aggregate function?
Yay, I've found out an easy way with your case by using some features in PostgreSQL 9.4+
Let's see this example:
select (array_agg(val ORDER BY i))[1] as first_value_orderby_i,
(array_agg(val ...
- 259
16
votes
Accepted
PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs
Probable bug on 9.6 and 9.6.1
This completely looks like a bug to me...
I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (...
- 13.5k
16
votes
Accepted
In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?
Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX ...
- 180k
15
votes
How to Convert Data to Proper Case In SQL Server
The challenge you'll run into with these approaches is that you've lost information. Explain it to the business users that they've taken a blurry, out of focus picture and despite what they see on t.v....
- 16k
15
votes
Accepted
Generic end date of quarter, PostgreSQL
I think the shortest and most elegant way to solve this issue is to use date_trunc('quarter',d) (which will retrieve the start of the quarter) + 3 months - 1 day, and use the expression to create a ...
- 13.5k
15
votes
Accepted
Can declaring function volatility IMMUTABLE harm performance?
Yes, it can harm performance.
Simple SQL functions can be "inlined" in the calling query. Quoting the Postgres Wiki:
SQL functions (i.e. LANGUAGE SQL) will, under certain conditions, have
...
- 180k
15
votes
Accepted
How to use variables in procedure (not function)?
SQL functions or procedures (LANGUAGE sql) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures ...
- 180k
14
votes
Are 'Functional Indexes' Supported in MS SQL Server?
While there isn't a feature of Functional Indexes in SQL Server per se, one can mimic a similar implementation via indexing on top of a Computed Column. Computed columns allow functions to be utilized....
- 38.6k
14
votes
Accepted
Why Does SQL Server Track Time Accurately For Some Multi-Statement Table-Valued Function Query Plans, And Not Others?
This is a consequence of using interleaved TVF execution.
Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and ...
- 88.3k
13
votes
Accepted
Find which functions are used
Yes, use the statistics collector. The manual:
PostgreSQL's statistics collector is a subsystem that supports
collection and reporting of information about server activity. [...]
It can also ...
- 180k
13
votes
Accepted
How to use an array as argument to a VARIADIC function in PostgreSQL?
Pass the array as is using the VARIADIC key word in the call:
CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[])
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL ...
- 180k
13
votes
Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?
In addition to @Paul's excellent Yes #1, there is actually a Yes #2 that:
works as far back as SQL Server 2005,
does not require setting a trace flag,
does not require that the computed column be ...
- 69.4k
13
votes
Accepted
Accessing the OLD/NEW table value in a trigger function in plain SQL
Firstly, to create a trigger on the order_items table, the update_totals() must be a trigger function which RETURNS TRIGGER (bold is mine).
A data change trigger is declared as a function with no ...
- 1,982
13
votes
Accepted
Why does a Set Returning Function (SRF) run slower in a FROM clause?
Let's start by comparing execution plans:
tinker=> EXPLAIN ANALYZE SELECT * FROM generate_series(1,1e7);
QUERY PLAN ...
- 401
13
votes
Accepted
Emulate user-defined scalar function in a way which doesn't prevent parallelism
You cannot really safely achieve exactly what you want in SQL Server today, i.e. in a single statement and with parallel execution, within the restrictions laid out in the question (as I perceive them)...
- 88.3k
13
votes
Accepted
SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?
There are various subtle differences.
Simple SQL functions can be inlined when nested in an outer query - provided they meet some preconditions. Not possible for a PL/pgSQL function.
All statements in ...
- 180k
13
votes
Accepted
Returning values for Stored Procedures in PostgreSQL
Both your sources are plain wrong.
A FUNCTION basically always returns something. void at the minimum, a single value, a row, or a set of rows ("set-returning function", a.k.a. "table-...
- 180k
12
votes
Why is the schema prefix (dbo) mandatory when we call a function?
The other answer explains that this is a restriction but not the reason why.
The requirement is not always true. Scalar UDFs can be EXEC-ed and still use implicit resolution (example).
I imagine this ...
- 85.9k
Only top scored, non community-wiki answers of a minimum length are eligible