112
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
128k16 gold badges297 silver badges444 bronze badges
97
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 = ...
86
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;
53
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, ...
46
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
128k16 gold badges297 silver badges444 bronze badges
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 ...
35
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 ...
33
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 ...
27
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, ...
22
Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
RETURNS VOID
LANGUAGE plpgsql 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$;
I am using format() ...
answered May 23 '13 at 17:01
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
22
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 "...
22
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 ...
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
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
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 ...
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
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,
...
14
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 ...
13
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
Data type ...
13
Is there an explanation for the cardinality estimate of 1.0003 for SQL 2014 while SQL 2012 estimates 31,622 rows?
I think @Zane's answer covers this part pretty well.
Is there a good workaround?
You could try creating a Non-Persisted Computed Column for LEN(cust_nbr) and (optionally) create a Non-Clustered Index on that Computed Column. That should get ...
13
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 AS
'SELECT json_extract_path_text(string::json, VARIADIC params)';
Call:
SELECT json_extract_path_text('{"f1":{"f2":1,"f3":"foo"}}', VARIADIC '{f1, f3}');
...
answered Dec 13 '16 at 23:57
Erwin Brandstetter
128k16 gold badges297 silver badges444 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
