Questions tagged [functions]

A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.

Filter by
Sorted by
Tagged with
8
votes
1answer
363 views

Why does a Set Returning Function (SRF) run slower in a FROM clause?

This is a database-internals question. I'm using PostgreSQL 9.5, I'm wondering why Set Returning Functions (SRFs), also known as Table Valued Functions (TVFs) run slower when in a FROM clause, for ...
2
votes
1answer
245 views

How to make PostreSQL functions private (inaccessible to end users)?

When writing a set of PostgreSQL functions with procedural languages, is it possible to make some of the supporting functions private and not accessible by end users? I wanted to refactor some common ...
3
votes
2answers
71 views

SQL - prevent a function from being executed in a short period of time

I'm using a lot of functions to make a website up and running, almost every action in that website leads to executing a SQL functions. Imagine it this way, almost all data processing and website logic ...
4
votes
2answers
13k views

SQL server create temporary function as a CTE

I need to create a temp function, for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables). Is there any way that I could use a CTE or a #temp query ...
0
votes
1answer
864 views

Mysql deterministic function, scope?

The documentation for MySQL isn't explicit on this point, but I'm wondering whether the scope for a function with respect to DETERMINISTIC is database global, or by some other scope? Consider this ...
0
votes
1answer
2k views

Syntax error, Hello World example. Begin … End [closed]

I borrow an example hello world function for mysql, but it throws me an error, I don't know where is the syntax error in my query. DROP FUNCTION IF EXISTS helloWorld; CREATE FUNCTION helloWorld( s ...
5
votes
1answer
1k views

Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error. create or replace ...
1
vote
1answer
214 views

Does bool_and short-circuit function calls with arguments?

Suppose I have the following query: SELECT a.* FROM a CROSS JOIN b GROUP BY a.id -- Primary key HAVING bool_and(expensive_comparison(a, b)) What will PostgreSQL do with this query? Will it process ...
2
votes
1answer
61 views

Limit on number of nested IF statements

Is there a technical reason when T SQL gives this error? Msg 191Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. My scenario is a ...
1
vote
1answer
108 views

MySQL test if function is available

Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course :-( Ref: https://dev.mysql.com/doc/...
6
votes
1answer
239 views

SQL Server 2016 concurrency limitations? Tuning for db concurrency

I have a table with ~1 billion timestamped records, and each record holds an FK to a session table (one session per day & 3-500,000 records per day), so finding the records for a given day is ...
3
votes
1answer
3k views

Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)

I have TBV function . Like this ALTER FUNCTION [dbo].[fn_Functiont] ( @accessibleIds ListAccesableIds READONLY ) RETURNS TABLE AS RETURN ( SELECT d.*, b.Name AS Name, ps.Name AS ...
2
votes
1answer
331 views

Oracle 11g: functions or procedures in C/C++

I want to create a function called by a trigger on oracle but in c or c++. Something like in PostgreSQL: Audit trigger in C. Or, in any case, is there support for this but in other languages like ...
1
vote
1answer
827 views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
-2
votes
2answers
288 views

Error when using a query as a function parameter [closed]

I have followed this tutorial for creating a string splitting function. When I try to run the function from the article using a SELECT as the first parameter, I get the following error: select '...
5
votes
1answer
11k views

Accessing the OLD/NEW table value in a trigger function in plain SQL

I am trying to build a trigger function to automatically update parent order values any time any of it's items changes. This is what I have created: CREATE OR REPLACE FUNCTION update_totals() ...
7
votes
1answer
1k views

Is the function PARSENAME() the opposite of QUOTENAME()

In the question Is there any (hidden) built-in function on MS-SQL to unquote object names? the author was seeking advice on if there was an internal function to "UNQUOTE" a quoted string. The author ...
0
votes
2answers
59 views

Calcutation at SQL level

I have this query: SELECT * FROM ( SELECT COUNT(lead_status) as 'Pending files' FROM vw_file_registrationlist WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || ...
0
votes
1answer
61 views

Check which condition has met - Sql Server

In a simple scalar function, I got several "IFs" separated each of them with a unique condition. Is there any way that I can check which condition was satisfied without having to change the function ...
0
votes
1answer
1k views

Aggregate JSON column

I have a table with a JSON column in Postgres, I am grouping the rows and I am looking for some aggregate function that could "sum up" values from jsons. sum aggregator sums the integers, I am ...
12
votes
1answer
773 views

How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

We have a SQL Server database which has a database audit specification which audits all execute actions on the database. CREATE DATABASE AUDIT SPECIFICATION [dbAudit] FOR SERVER AUDIT [servAudit] ADD ...
1
vote
1answer
2k views

Split two delimited strings in same order without function

I am trying to split two columns with delimited strings into rows. The positions of the values in each string are related so I am trying to split it out so that the related values are in a row. I am ...
1
vote
2answers
3k views

Postgresql - Can I do a join in a function?

I'm recently starting to use PostgreSQL. In SQL Server I used to use stored procedures for different kind of things. I want to know if, like in SQL Server, can I do something like in that. I used to ...
5
votes
2answers
4k views

In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?

I understand the distinction between: Scalar functions Set-Returning-Functions (SRF)s Internal functions Window functions Aggregate functions of all sorts User-Implemented functions (which in ...
0
votes
1answer
521 views

“ORA-12899: value too large for column ”V_UUID“ (actual: 36, maximum: 4000)” when used to generate a Virtual column

I have the following function: CREATE FUNCTION UUID_AS_HEX(bytes IN RAW) RETURN CHAR DETERMINISTIC IS uuid CHAR(36); BEGIN RETURN REGEXP_REPLACE(bytes, '([0-9A-F]{8})?([0-9A-F]{4})?([0-9A-F]...
1
vote
1answer
50 views

Creating a MySQL function gives an error

This is the error after running this script: MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ...
0
votes
1answer
8k views

SQL Error [42883] - function does not exist - Cannot call function from trigger

I want to insert a new row to Table2 when Table 1 is inserted a row. I have created a function for that. However, when I try to create the trigger, it gives an error although function is created and I ...
7
votes
2answers
196 views

Is a dropped (or altered) function still available inside already opened transactions?

I found PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions? but there are no answers and isn't exactly the same as my question (though very similar). ...
2
votes
1answer
1k views

How to make a function return a dynamically named column?

Simple pseudocode example: create function myfunc (someparam text, desired_return_column_name text) returns table (somekeyname int, MAGIC_DYNAMIC_NAME text) language SQL as $funcdef$ select ...
1
vote
1answer
299 views

Partial updates with functions

My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided. Let'...
0
votes
1answer
2k views

Table-Valued Functions from Linked Server

Why does when I try to run: SELECT * FROM [servername].master.sys.dm_os_volume_stats(1, 1) AS btb (NOLOCK) I get error: Msg 195, Level 15, State 15, Line 45 'servername.master.sys....
-1
votes
2answers
628 views

What internal functions are undocumented in SQL Server?

I just read about fn_dblog, and fn_dump_dblog. I was wondering how do I know what functions like this exist, I'm not the only one either, the comments are scattered with people wanting to know more, ...
0
votes
0answers
444 views

table type parameter vs temp table vs table variable

I am trying to create a function that will take a table and return a VARCHAR(MAX). The source table the function will be called against is not constant so I cannot hard-code a table name into the ...
2
votes
1answer
967 views

Performance of XML to Table Function in Stored Procedures

In a number of the databases I'm helping to maintain, there is a pattern where the code passes in a list of IDs as an XML string to a stored procedure. A user-defined function turns them into a table ...
-2
votes
1answer
188 views

Signing a User-Defined Function with a certificate in SQL Server

I'm trying to use a certificate to sign a user-defined function in SQL server (2016). My code is as follows: CREATE FUNCTION myfunction() RETURNS TABLE AS RETURN SELECT col1, col2 FROM ...
-2
votes
1answer
459 views

Why can't a function that accepts an int, be overloaded to accept an interval?

Currently getting an error about type casts, HINT: Could not choose a best candidate function. You might need to add explicit type casts. Let's say I create an overloaded function foo CREATE ...
9
votes
1answer
5k views

Postgres : Truncate if exists in psql function with parameter

I am trying to get a psql function that would truncate a given table name if it exists. I've been trying multiple functions, but none of them worked so far. Here is the code : CREATE OR REPLACE ...
0
votes
1answer
63 views

PostgreSQL 10 - single big query in UDFs or multiple smaller ones?

Many times, I'm able to write a PL/PGSQL UDF in two ways: As a big query usually using WITH clauses and UNION (i.e. more declarative but also more complex to write/read) As multiple smaller queries (...
1
vote
1answer
78 views

Function taking a string as table and column name

I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table: CREATE OR REPLACE FUNCTION survey_participated(_sn text) ...
2
votes
2answers
2k views

Ideal way to return a scalar from function (RETURN vs. SELECT)?

There's a blog for SQL Server that says returning a scalar using SELECT is wasteful: SELECT should be used for returning resultsets, not scalars. This procedure uses a SELECT statement to return a ...
4
votes
2answers
948 views

How to completely hide a function from all other users and roles?

Database : Postgres 9.6 Use case : I have a function that needs to be hidden, only a particular role/user can see it. I don't even want to expose even the name or body of function to other users/...
0
votes
1answer
93 views

Fix for slow SQL_INLINE_TABLE_VALUED_FUNCTION

My query with two UDT parameters takes 0.3 seconds but when the same code is encapsulated into an inline table valued function it takes 3.5+ seconds. I've spend days researching/attempting fixes (...
4
votes
1answer
339 views

SQL Server 2017: How does Interleaved Execution work?

Interleaved Execution is part of a family of features in the 2017 query processor that consists of: Batch Mode Adaptive Joins Interleaved Execution Batch Mode Memory Grant Feedback So how does ...
5
votes
2answers
1k views

Optimizing a Query for splitting strings with a known size

I have a Column that has a . as a delimiter, it looks like so.... abc.efg.hij I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this ...
2
votes
1answer
67 views

Using a trigger function to constrain values according to those already in the table (postgresql)

I'm just learning SQL and I can't seem to find out how to do this. The table is (id, subject, year, grade). I'm supposed to reject any inserts or updates for a student if he has scored an F before ...
29
votes
2answers
2k views

Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results. There are some places where a Scalar UDF is the only option, though. As an ...
0
votes
1answer
733 views

Sum duration for each day of a month

Question: I would like to create a query that can return a list of all days of the month with a sum of all duration for each day including zero for each day that has no data. Database: PostgreSQL 9.x ...
7
votes
2answers
698 views

Why high compile times for functions with XML on SQL Server 2016?

We have inline functions which gather up data into XML, pass that derived XML to other functions, which then shred it and reform it as a string. (Your "you shouldn't do that sort of stuff in T-SQL" ...
0
votes
1answer
63 views

Mathematically compare multiple 3 rows sets of table and record result in new column

I need to isolate 3 row groups in my overall query and perform the same CASE statement on each group and return a result in the final output I believe i may have to use a CTE (which i have not done ...
0
votes
1answer
65 views

Want to perform a function on a returned table on subset of rows

I have a query that takes 10 minutes to run (1.4 million rows and 15-ish joins), so I think the time isn't terrible. What I need from this is to effectively INNER JOIN on itself an additional column ...

1 2 3
4
5
12