99

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 ...


81

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 = ...


76

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;


41

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, ...


37

You can do this: UPDATE table_name SET column=lower(column) Refer to www.postgresql.org/docs/9.1/static/functions-string.html


35

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 ...


32

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 ...


29

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 (...


26

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 ...


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 ...


24

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, ...


21

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair. Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. ...


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 Selectivity ...


19

Quite simply: Null VaLue The function substitutes any NULLs in a given resultset column with the value given as the second parameter.


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

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 ...


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 indeed 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 ...


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

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, ...


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

\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 ...


15

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 ...


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 ...


14

A function attempts to return something, always, and has several restrictions - for example, you can not have any side effects, so you can't issue DML, call stored procedures, use dynamic SQL, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in older versions, at least in SQL Server 2000 - ...


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

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 ...


Only top voted, non community-wiki answers of a minimum length are eligible