Tag Info

Hot answers tagged

20

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


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

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


11

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


11

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


8

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


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

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


8

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


7

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


7

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


7

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


7

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


7

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


6

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


6

This sort of thing gets complicated. I am working on some related projects right now. The basic tweak is that PostgreSQL uses a format which uses double quotes internally in tuple representation to represent literal values, so: SELECT save_book('(179,the art of war,fiction,"{190,220}")'::book); should work. In essence a neat trick is creating a csv and ...


6

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


6

You don't need a the function at all, this can be done with a single SQL statement: with recursive tree as (id, parent) ( select link as id, parent from linktable where id = itemid union all select c.link as id, c.parent from linktable c join tree p on p.id = c.parent ) select dt.id, dt.value from tree ...


6

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


6

Uh, why not just select /* user.columns, */ /* company.columns, */ NumDays = DATEDIFF(Company.CreatedOn, curdate()) from dbname.company inner join dbname.user on user.company_id = company.company_id where Company.CreatedOn < curdate() - INTERVAL 365 DAY;


5

I don't know if there is way to define a new aggregate function, not without messing with MySQL source code. But if your numbers are all positive, you may well derive from the arithmetic identity: log( product( Ai ) ) = sum( log( Ai ) ) that you can use EXP(SUM(LOG(x))) to calculate PRODUCT(x). Test in SQL-Fiddle: SELECT EXP(SUM(LOG(a))) AS product FROM ...


5

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


5

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


5

Because retVal starts out as null,and null || something stays null. You should initialize retVal as ''.


5

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


5

Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but... How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans? Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics. Table rows ...


5

Use GET DIAGNOSTICS integer_var = ROW_COUNT; More in the manual in the chapter Obtaining the Result Status. Your example could look like this: CREATE OR REPLACE FUNCTION f_test() RETURNS SETOF table_a AS $func$ DECLARE i int; ct int := 0; BEGIN RETURN QUERY SELECT * FROM table_a; -- 14 records GET DIAGNOSTICS i = ROW_COUNT; ct := ct + i; ...


5

I think yes. Haven't tried with actual INSERTs or UPDATEs yet, but this function works: CREATE TABLE a (id integer); CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS $body$ SELECT b.* FROM unnest($1) b; $body$ LANGUAGE sql; This way you can write your INSERT statement as INSERT INTO tbl_weightment SELECT (r).* FROM unnest(rec) ...


5

You have actually asked two questions: "If a query is written in MySQL SP/Triggers/Functions/Events is slow, it will not be logged into slow log file" Answer: it will not be logged in standard MySQL server. Do take a look at the Percona Server extension for the slow log: in particular look at the log_slow_sp_statements configuration variable. It does what ...


5

Create a computed column which converts your lat and long columns into a geography type using the Point constructor. Then put a spatial index on this computed column. Then your query can create a geography point from your circle centre, and compare distances. Should be very quick.



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