105
Index names in PostgreSQL
Index names are unique across a single database schema.
Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
Two tables in the same schema cannot have an index of the same name. (Follows logically.)
If you do not care about the name of ...
answered Feb 27 '13 at 22:22
Erwin Brandstetter
118k16 gold badges265 silver badges406 bronze badges
92
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 = ...
84
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;
51
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, ...
44
You can do this:
UPDATE table_name SET column=lower(column)
Refer to www.postgresql.org/docs/9.1/static/functions-string.html
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 ...
40
It depends.
SQL functions
With LANGUAGE sql, the answer is generally yes.
Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from the body and pass parameters.
PL/pgSQL functions
With LANGUAGE plpgsql, the answer is normally yes.
However, PL/pgSQL allows for dynamic SQL where passed ...
answered Sep 12 '13 at 10:42
Erwin Brandstetter
118k16 gold badges265 silver badges406 bronze badges
33
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 ...
31
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 ...
26
It's ODBC escape syntax, and the engine knows what its own implementation is, and swaps it out, as you've seen in the execution plan. There are also other things, such as:
SELECT {fn curdate()},
{ts '2016-05-24 15:19:36'}, -- not vulnerable to SET LANGUAGE!
{guid 'D08891B4-BC25-4C7C-BAEF-3B756055AC6E'};
See the documentation here, here, here, ...
24
In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction ...
22
You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See ...
21
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 "...
20
I wouldn't do this with a loop; there are much better alternatives. By far the best, when you have to split, is CLR, and Adam Machanic's approach is the fastest I've tested.
Next best approach IMHO, if you can't implement CLR, is a numbers table:
SET NOCOUNT ON;
DECLARE @UpperLimit INT = 1000000;
WITH n AS
(
SELECT
x = ROW_NUMBER() OVER (...
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
Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
CREATE TABLE IF NOT EXISTS %I (
id serial PRIMARY KEY,
customerid int,
daterecorded date,
value double precision
)', 't_' || t_name);
END
$func$ LANGUAGE plpgsql;
I am using format() with %I to sanitize ...
answered May 23 '13 at 17:01
Erwin Brandstetter
118k16 gold badges265 silver badges406 bronze badges
19
First, 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 ...
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
Update Jan 2017 - SQL Server 2016+ / Azure SQL Database
SQL Server 2016 and the current version of Azure SQL Database now has the following syntax for functions, procedures, tables, databases, etc. (DROP IF EXISTS):
DROP FUNCTION IF EXISTS dbo.fn_myfunc;
And SQL Server 2016 Service Pack 1 adds even better functionality for modules (functions, procedures, ...
17
\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+------------------+--------------------------+--------
...
public | decrypt ...
17
And nothing about the functions. Why is the function information
missing in the actual plan?
This is by design, for performance reasons.
Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row. Put another way, the function body is executed separately for each input row. This single fact explains most ...
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
You should be able to use auto-explain. Turn it on and
SET auto_explain.log_min_duration = 0;
and you should get the plans in your log for all statements run in that session.
You might also want to set
SET auto_explain.log_analyze = true;
but you'll essentially run everything double - once for 'real' and once to EXPLAIN ANALYZE on. During a non-...
16
I addition to @rfusca's advice: SQL statements inside plpgsql functions are considered nested statements and you need to set the additional Parameter auto_explain.log_nested_statements.
Unlike some other extensions, you don't have to run CREATE EXTENSION for this one. Just load it dynamically into your session with LOAD. Your session could look like this:
...
answered Aug 29 '12 at 15:10
Erwin Brandstetter
118k16 gold badges265 silver badges406 bronze badges
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 ...
15
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
