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.
572
questions
0
votes
2answers
34 views
How to update data with a trigger function and a join
I have two relations :
CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int);
CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int);
INSERT INTO ...
0
votes
0answers
13 views
Function in Postgres that uses the table where I'm passing the function inside the function body?
I've got two tables, one with street_codes and street_numbers (addresses) and another with sidewalks_keys that correspond to each address (address_sidewalks_keys). I would like to create a function in ...
1
vote
3answers
65 views
How to calculate end_date from start_date and duration in SQL Server Query
I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.
I am providing the start_date and duration as the input to calculate end-date.
While calculating the end_date the ...
0
votes
1answer
24 views
How to pass a newly INSERTed row to a function?
I have following tables:
table1: table2:
| uid | did | | uid | did |
|-----+-----| |-----+-----|
| 1 | 2 | | '1' | 2 |
| 4 | 2 | | '4' | 2 |
| 2 | 3 | | '2' | 3 |
| 1 | 8 ...
-2
votes
1answer
27 views
Function to convert time difference between two dates into timer format
Does anyone have a function that converts date difference between two dates into timer format ?
For example:
select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35')
The result should ...
1
vote
1answer
37 views
Does “Table Valued Function” force the plan to get into cache?
sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
0
votes
1answer
45 views
relation “table” does not exist
i created the following schema , inside that schema i created a table and inserted a some values.
After that i created a function that get a value in the table based on an ID.
Here is all the ...
0
votes
1answer
15 views
How to list all functions with arguments that a role is allowed to execute in Postgres?
I found how to list tables a role has permissions on. I found how to list all functions with arguments.
My question is: How to list all functions with arguments a role is allowed to execute?
34
votes
1answer
2k views
SQL Server 2019 executes unreachable code
Consider the following repro example (fiddle):
CREATE FUNCTION dbo.Repro (@myYear int)
RETURNS datetime
AS
BEGIN
IF @myYear <> 1990
BEGIN
RETURN NULL
END
DECLARE @...
2
votes
0answers
32 views
Why does sql server consider a function that extracts a value from an xml parameter non-deterministic?
I want to add a computed field that extracts a value from another xml column.
The function looks like this:
create or alter function extractValue(@val xml) returns varchar(14)
as
begin
return @val....
1
vote
3answers
30 views
Tabled Value Function returns incorrect or no results
I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well.
Here is my code:
USE [RMC_Tracker] GO
/****** Object: ...
1
vote
2answers
17 views
Insert to a table using function with default arguments
I'm trying to write a function that makes an insert to the table defined below:
DROP TABLE IF EXISTS pl_table;
CREATE TABLE pl_table
(
id SERIAL PRIMARY KEY,
mandatory VARCHAR NOT ...
3
votes
1answer
49 views
generating all substrings (n-grams) for bit-strings
I followed SELECT all substrings (n-grams) of length n? to get a function for generating all n-grams for text types. Works great. I figured out how to cast my bit-strings to text, and the function ...
0
votes
0answers
7 views
New dataframe for different groups
I have a dataframe with a column 'Country'. Every country has multiple records. I want to write a function in python using pandas so that for every country it can return a seperate dataframe.
Having ...
4
votes
1answer
42 views
Generate due dates using function in a select statement
I want to create a semi - monthly amortization schedule by using a function.
Here is my function:
CASE WHEN day(@dt) < 16
THEN
CASE WHEN month(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF ...
0
votes
2answers
33 views
How use raise notice in nested and sub-function?
I'm using PgAdmin to create multiple pgsql functions.
I'm beginner on this environment.
I'm using 'raise notice' to help me for debugging phase.
I have two functions with 'raise notice' statement.
...
0
votes
0answers
9 views
Function returning a table has open parenthesis and single quote mark in results
I have a function returning a table and in the results I have weird results.
Result should be (A,somereturnedtextvalue), what I get is (A,"somreturnedtextvalue
There is no closing quote or ...
0
votes
1answer
17 views
Need a function to run different queries based on parameters sent subquery must return one column error
I am new to advanced sql programming. I am storing constants.
Constants have three possible scopes: local, user and global.
Two tables:
s0constants_user holds set values of constants
s0constants ...
0
votes
2answers
27 views
How to sort aggregate function call
If I have an expensive function call that takes a JSONB and returns an INT, e.g.
CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS
How do I go about ordering the aggregate result ...
0
votes
2answers
37 views
Trouble when accessing old after deleting a row in postgrsql
When I delete an athlete from my database I want to take the sum he has raised and divide the sum between all the other athletes in my database.
When I run my code "addBalance" becomes Null and ...
6
votes
0answers
173 views
Select permission denied when using linked server in a function unless inlining is off
We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production ...
0
votes
1answer
39 views
Does setting Query Optimizer Fixes to ON enable Scalar UDF Inlining on a database with compatibility level 140?
Azure SQL Database Managed Instance. Current Compatibility Level is set to 140 to match on-prem SQL Server 2017 test and development server. I just read this article on Scalar UDF Inlining and was ...
1
vote
1answer
27 views
Update record with converted epoch timestamp from another column
I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it ...
1
vote
1answer
43 views
character varying vs. text as function parameter types in PostgreSQL
Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples:
Example 1:
CREATE OR REPLACE ...
4
votes
1answer
204 views
Changing to compatibility level 150 makes SQL Server consume all available memory
We're moving an existing DB to a new SQL Server 2019 instance.
If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available ...
0
votes
0answers
15 views
Create postgresql function to be triggered before insert row
I've created a function to be executed before inserting rows in a table, in order to avoid the error "malformed array literal". It still throws the error and I deduct that the process starts earlier ...
1
vote
1answer
589 views
Postgres get function owner
I would like to be able to get a table of function names and function owner names.
Currently, this query returns a list of functions for a given schema:
SELECT proname
FROM pg_catalog....
0
votes
2answers
45 views
Encoding issue mysql
I'm using the MD5 hash function of MySQL to hash some data I import into the database from a CSV file.
The issue is that some lines have an incorrect hash (but not all of them).
When I use a query:
...
0
votes
1answer
25 views
Wrong result for FileTableRootPath Function of SQL Server
I just restored a database backup and now the result of the function FileTableRootPath() is incorrect.
Where is the place that I can change to change the result of this function?
0
votes
1answer
36 views
Listing function input and output details for stored functions in Postgres 11.5+
I'm trying to build a query that extracts the input and output definitions for the functions in a schema. We've got multiple client types written in multiple languages, and plan to move many queries ...
1
vote
1answer
32 views
In Oracle 12c, is there an alternative to CREATE TYPE when I only have read-only privileges?
I have read-only access to a Oracle 12c system where there is zero chance I can get additional privileges or get the DBAs to change anything on my behalf (such as do CREATE TYPEs for me).
I'm using ...
0
votes
1answer
12 views
How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever
I am looking for how to set the value of @MaxItemSeqNum
to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement.
I need this in order to insert the next record ...
1
vote
1answer
81 views
54001 stack depth limit exceeded in recursive stable function [closed]
I have a function which calls itself to resolve a hierarchy. Without any volatility declaration, it gives me the desired result (that means the recursion ends). As a performance optimization, I ...
2
votes
1answer
61 views
calling scalar function to assign value to local variable
In a SQL Server stored procedure, if we declare a local variable and assign the value by calling a scalar function
DECLARE @variable_name INT= dbo.scalarfuntion_name(@p1, @p2).
Would it have any ...
7
votes
2answers
472 views
After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019
We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL ...
0
votes
1answer
19 views
Extract return type of functions from metadata
I want to identify all my stored functions which return REFCURSOR.
Is there a way to do that?
I can't find this information anywhere.
I searched in dba_functions,dba_objects,dba_arguments...
What ...
1
vote
2answers
2k views
SQL Server works slow after enable XP_CMDShell even disable until restart service
2 days ago I created a function that needs INSERT and UPDATE, and I enabled XP_CMDShell and do it by running a script.
Afterwards, SQL Server works very slow for SELECT commands. Even very simple ...
-1
votes
1answer
82 views
Can I use built-in (deterministic) SQL functions in a Computed Column or do I need to create my own function as a passthrough to leverage them? [closed]
I'm trying to create a (persisted) computed column on a table that is essentially:YEAR(DateColumn). When I try to directly call the YEAR() function inside the Formula of the Computed Column ...
-1
votes
1answer
52 views
SQL Variables inside SELECT query in a function
MariaDB 10.3.14
I have a function that generates the root id in a hierarchy:
The function is this:
CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE ch INT;
...
1
vote
1answer
69 views
How to select functions that belong in a given extension in PostgreSQL?
I am trying to compose a SELECT from pg_catalog that pulls function name, its schema name and the name of extension that created this function.
To pull function list I use (a simplified version)
...
2
votes
3answers
290 views
SQL Server 2017 Stored Proc & TVF Performance Problem
I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an ...
0
votes
1answer
30 views
Remove extra return from median calculation
I have a FileMaker database where I want to calculate the median value of 3 different fields. I used the code that can be found here.
In my setup, it looks like this:
Let ([
beak.trait = List (...
0
votes
1answer
29 views
Trigger function error: Record “old” not yet assigned
I'm new to trigger functions and trying to work this out. I have looked for solutions in the documentation and applied those, but still something is wrong here. My aim is to show error an message when ...
0
votes
0answers
15 views
How can I split a function into multiple smaller functions that serve multiple purpose?
I first made a post on CodeReview regarding this query and how it could be optimized while I kept trying and thinking different approaches.
This is a dbdiagram.io of the tables of the database that ...
11
votes
1answer
678 views
After upgrading to SQL Server 2019, function throws “insufficient memory” error
I have moved an SQL Server 2012 Database (8GB in size) to a newly setup SQL Server 2019 virtual machine with the same memory and CPU configuration and changed the compatibility level to SQL Server ...
0
votes
0answers
30 views
Function prefixed with 0x
I have a table created with
Create table log (
ip VARBINARY(16)
);
I can insert into the table with Insert into log values (INET6_ATON("127.0.0.1")).
When I use phpMyAdmin 4.5.4.1 in the "...
1
vote
3answers
195 views
Having md5(NULL) return a non-NULL value in postgresql
I have a table with the structure (content_md5 UUID, content TEXT), where I'd like to use content_md5 (which is the value of md5(content)) as the primary key, and use it as a foreign key in other ...
1
vote
2answers
51 views
How to pass collation to UDF
How can we pass the collation to a user-defined function?
e.g. I can use a hardcoded collation (e.g. "de-AT-x-icu") like this:
CREATE OR REPLACE FUNCTION test(agg_1 jsonb, agg_2 jsonb) RETURNS jsonb
...
11
votes
1answer
155 views
Inline Scalar UDF Under RCSI - Can Results Differ
SQL Server 2019 introduces Scalar UDF inlining, aka "Froid." This ".. embeds [scalar UDFs] in the calling SQL query."
Previously, scalar UDFs ran under their own execution context, separate from the ...
1
vote
0answers
44 views
Need suggestion on optimizing the query which calls a scalar function
--gets the configuration (team or client or default)
CREATE FUNCTION [dbo].[get_config]
(@TEAM_CONFIG BIT, @CLIENT_CONFIG BIT, @DEFAULT_CONFIG BIT)
RETURNS BIT
AS
BEGIN
DECLARE @bit BIT = ...
