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
4
votes
1answer
33 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
28 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
8 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
26 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
130 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
27 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
20 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
33 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
161 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
156 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
38 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
21 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
29 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
11 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
49 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
41 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
338 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
17 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
64 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
50 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
46 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
264 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
25 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
26 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
645 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
120 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
48 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
153 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 = ...
3
votes
3answers
312 views

Query slow when using function in the WHERE clause

This is fast (49ms): v_cpf_numerico := ext.uf_converte_numerico(new.nr_cpf); select cd_cliente into v_cd_cliente from central.cliente where nr_cpf_cnpj = v_cpf_numerico; This is slow (15 seconds): ...
0
votes
1answer
50 views

Computed Columns - SQL Server Function

I'm looking to improve overall SQL performance of a customers application. On each table of the application, there is a computed column, that uses GETUTCDATE and a combination of three columns on that ...
1
vote
2answers
68 views

Is it possible to write a function in postgresql which directly writes its parameters to a text file?

I have a text file which contains some rows of data,say 10 rows. Each row has 5 columns, each of the type varchar. Is it possible to write a function in postgresql, which takes its parameters as 5 ...
2
votes
1answer
144 views

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

The manual for Postgres 12 says: The following operations are always parallel restricted. Scans of common table expressions (CTEs). Scans of temporary tables. ... Further down on ...
1
vote
2answers
51 views

Trigger function does not exist, but I am pretty sure it does

I have a procedure: create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp) as $$ begin update sensor set last_read = read_time where ...
0
votes
2answers
73 views

Table function in oracle

I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that ...
1
vote
1answer
41 views

Get multiple columns from a function depending on outer group by

I have first a selection of trip_id (for a train or a bus e.g.) in a (sub-)Table A trip_id 350 351 360 and want to use a self-written function which takes the used trip_id and gives back the a ...
-1
votes
1answer
21 views

null value in postgres functions

I am new in postgres and i found this issue with this simple function CREATE OR REPLACE FUNCTION "public"."TEST"("disponibile" int2=0, "extra" int2=0, "ordini" int2=0, "prenotata" int2=0) RETURNS "...
0
votes
1answer
147 views

PostgreSQL - How to create a function to validate a field is DateTime?

I would like to create a function to validate a field from an import table and ensure the field is in 'DateTime' format. I am not entirely sure how to do this and would appreciate any help? Function ...
0
votes
1answer
44 views

MADlib apply `cosine_similarity` on term frequency

I am working on a small topic modelling project and I have stumbled upon the idea of developing it in MADlib and PostgreSQL. MADlib provides this term frequency function that converts my documents ...
0
votes
0answers
13 views

Custom methods in MySQL / MariaDB ORDBMS

According to the book I am reading ORDBMSs come with an option to write a program (method) in Java (or other programming language), compile it into Java byte code and register via some SQL command. ...
1
vote
0answers
53 views

Optimising filter with dynamic computed column

I need to implement user risk scoring in model The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, ...
0
votes
1answer
23 views

Is there a postgres function documentation pattern that is similar in concept to javadoc?

I am mostly a Java developer. Today I am writing some postgres functions. Out of habit I keep trying to document these functions using a Javadoc-like comment style describing the function parameters ...
3
votes
2answers
43 views

How can I determine which permissions needs to be assigned to the newly created login?

We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use ...

1
2 3 4 5
12