106
You need CROSS APPLY not join.
The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table.
select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...
87
Yes.
Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question).
This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every ...
60
If you are using SQL Server you can use the REVERSE() function to check?
SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome;
Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function:
SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;
57
As said in "40.5.3. Executing a Query with a Single-row Result" (emphasis mine):
The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause.
So this should work:
SELECT col1, ...
42
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 that function.
Using a variable instead of a constant shows a bit more detail
declare @myYear int = 0
SELECT dbo.Repro(@myYear);
Node 5 defines Expr1000 = CASE ...
38
Scalar functions require EXECUTE permissions, however when you've converted to a Table Valued Function the permissions required change to SELECT.
You must now GRANT SELECT ON functionName TO another_user;
From BOL:
Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a ...
35
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 required:
Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2016 RTM
Cumulative Update 6 for SQL Server 2014 SP2
BUT to avoid a bug (...
31
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 execute subqueries directly, so they are always unrolled or converted to an apply.
The semantics of CASE are such that a THEN expression should only be evaluated ...
29
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 rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "...
26
The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:
EXPLAIN ANALYZE SELECT f1();
Optimization
PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for ...
20
For the legacy CE, I see the estimate is for 3.16228 % of the rows – and that is a "magic number" heuristic used for column = literal predicates (there are other heuristics based on predicate construction – but the LEN wrapped around the column for the legacy CE results matches this guess-framework). You can see examples of this on a post on ...
19
Using OUT parameters achieve basically the same thing as in @klin's answer, but without creating user-defined types. Just move all your variables from the declare block into the argument-list as OUT parameters:
create or replace function get_user_info(
IN _id varchar,
OUT is_banned boolean,
OUT reputation integer,
OUT is_vip boolean,
...
18
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's one heck of a dry, boring read.
Q: Do ANSI standard SQL functions change data in the database?
No. You can use them to change data - for example, I can say:...
17
It is fairly well documented that UDFs force an overall serial plan.
I'm not certain it is all that well documented.
A scalar T-SQL function prevents parallelism anywhere in the plan.
A scalar CLR function can be executed in parallel, so long as it does not access the database.
A multi-statement table-valued T-SQL function forces a serial zone in a plan ...
17
Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a typo mention it in the comments and I'll explain what's going on. I'm going to point out several things that you may already know, so please don't take offense ...
17
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 get something that works with the current version of SQL Server, but it's possible that future changes will require you to revisit your code.
If it's possible ...
16
Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of SELECT, UPDATE, and DELETE queries. You cannot, however, use a Scalar UDF in any of those contexts.
Secondarily, you can also EXECUTE a Scalar UDF. This syntax ...
16
Postgres 11 or newer
SQL procedures ("stored procedures") that can also start and end transactions:
The manual for CREATE PROCEDURE.
2ndquadrant blog entry introducing the feature.
However, commands that are not allowed in a transaction block can not (yet) be included. Important examples CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM.
...
16
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 (in version 9.6.0 and 9.6.1).
CREATE TABLE users
(
id SERIAL PRIMARY KEY,
email TEXT NOT NULL,
column_that_we_will_drop TEXT
) ;
-- Function that ...
16
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 CONCURRENTLY or VACUUM. The manual:
VACUUM cannot be executed inside a transaction block.
Functions are often called "stored procedures", which has always been a ...
16
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 most direct T-SQL representation of the in-lined function produced by SQL Server 2019 for your scalar user-defined function. One wouldn't rewrite the function this ...
15
First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also, misleadingly, called "procedure") with a function body and 0-n triggers (without body) calling this function.
The special variable NEW in plpgsql trigger functions is neither a map nor an array; it's a record holding the new row:
NEW
...
15
The way statement_timeout works, the time starts counting when the server receives a new command from the client.
Queries launches inside server-side functions are not commands from a client, they don't reset that timer or push a new one onto a stack of timers.
This is why SET LOCAL statement_timeout = 100; has no effect.
And if a function does SET ...
15
You could also use a Numbers table approach.
If you don't already have an auxiliary numbers table you can create one as follows. This is populated with a million rows and so will be good for string lengths up to 2 million characters.
CREATE TABLE dbo.Numbers (number int PRIMARY KEY);
INSERT INTO dbo.Numbers
(number)
SELECT TOP 1000000 ...
15
The parameter _source in the MWE (minimal working example) is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name independently.
But it would not work like this anyway. SQL only allows to parameterize values in DML statements. See:
Error when setting n_distinct using a ...
15
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 executed.. So you have a chance to modify the :NEW (old :OLD in case of DELETE) row BEFORE it is inserted/updated/deleted... But anything you want to do you can do (...
14
There is no way to define stored procedures or stored functions (or events) that are global.
One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();).
This is something I do with my collection of custom date/time calculation functions (e.g., ...
14
Two queries - two replies:
a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative ...
14
To drop all functions (including aggregates) in a given schema (be careful with this!):
Postgres 11 or later
Procedures have been added.
The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures
DO
$do$
DECLARE
_sql text;
BEGIN
SELECT INTO _sql
string_agg(format('...
13
It will be available in 9.5.
Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947
Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html
Only top voted, non community-wiki answers of a minimum length are eligible