Hot answers tagged

52

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


18

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


16

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


15

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


15

Okay so this is what I've been able to find with some digging. 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 ...


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


13

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


12

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


11

The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds: there is only one copy of the object to maintain the developer doesn't have to provide three- or four-part names e.g. USE UtilityDB; GO CREATE FUNCTION dbo.LastIndexOf(...) ... GO USE ...


11

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


11

There are two problems here: For Multistatement TVFs, you just need RETURN; instead of RETURN @variable;. It does not appear as though you can use a User-Defined Table Type (UDTT) as the return table type. That will need to be specified explicitly (i.e. each column name and datatype). If this were a Scalar UDF, then the syntax of specifying only the ...


10

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 then create a Non-Clustered Index on that Computed Column. That should only ...


10

It's ODBC 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 things like {fn curdate()}. Some are documented here (note that Now() isn't listed), but please don't investigate and learn about this syntax; IMHO you should use the native syntax and pretend you've never heard of ...


10

Using T-SQL scalar functions will frequently lead to performance problems* because SQL Server makes a separate function call (using a whole new T-SQL context) for each row. In addition, parallel execution is disallowed for the whole query. T-SQL scalar functions can also make it difficult to troubleshoot performance problems (whether those problems are ...


9

This is easy if you have a numbers table. The following example is cut and pasted from Erland Sommarskog's site: CREATE FUNCTION fixbinary_single(@str varbinary(MAX)) RETURNS TABLE AS RETURN(SELECT listpos = n.Number, n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4)) FROM Numbers n WHERE n.Number <= ...


9

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


9

A LANGUAGE sql function must be a complete SQL statement (or more than one). Yours is just an expression. Just prepend SELECT, like you would when running it stand-alone. $BODY$ SELECT (SELECT ... The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query: regress=> ...


9

Stop using REAL and use DECIMAL instead. REAL and FLOAT are approximate data types and can't represent all numbers in the nice, rounded way you are used to. Compare these: DECLARE @a REAL = 0.23, @b REAL = 0.24; SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x; Result: 0.234999999403954 Now using DECIMAL instead: DECLARE @a DECIMAL(10,4) = ...


8

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.: GRANT SELECT ON dbo.Table_Valued_Function TO [testuser]; So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much ...


8

In T-SQL, you cannot modify any data in a function. There is no straightforward way around it. There are some obscure hacks, but I would not use them. Use a stored procedure. The hack, quoting from Erland Sommarskog: CREATE FUNCTION loophole(@i int) RETURNS varchar(20) AS BEGIN DECLARE @sql varchar(MAX), @cmd varchar(4000) SELECT ...


8

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


8

The query optimizer treats an inline table valued function exactly like a view: CREATE FUNCTION dbo.InlineUdf(@arg1 int) RETURNS TABLE AS RETURN ( ... your query here ... ); A multi-statement table-valued function is run more like a stored procedure. They typically have to be executed multiple times, rather than be folded into the main query: ...


8

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


7

I suggest an SQL function: CREATE OR REPLACE FUNCTION foo(_date date) RETURNS TABLE ( name text -- types have to match your actual types! , keyword_id int , project_id int , the_date date , today int , yesterday int , week int , month int) AS $func$ SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col ...


7

A function-based index adds a virtual column to the table (This column is then indexed). Dropping the index removes the virtual column, which leads to a cleanup that takes time (same amount of work as the removal of a non-virtual column).


7

The easiest way I can think of is the following: Right-click on your database -> Tasks -> Generate Scripts... Select specific database objects Select the checkbox User-Defined Functions Save to file and just name your text file whatever you'd like This will generate and save the scripts for your UDFs to your desired file. You could use PowerShell (off ...


7

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


6

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


6

Method 1 In Object Explorer, expand your [server] => [database] => Programmability => Functions and highlight the appropriate [function type] (e.g. Scalar-valued Functions). Now open Object Explorer Details (F7). In the right-hand pane, select all the functions, then right-click, Script Function As => CREATE To => File. I don't think you can use this ...


6

Peter's quite right about the problem, of course. However, this entire function is unnecessarily slow and complex - that repeated string concatenation will be horrible for performance, and PL/PgSQL loops are best avoided when you can anyway. You can do the same job with an ordinary SQL function using generate_subscripts: CREATE OR REPLACE FUNCTION ...



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