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
0
votes
1answer
29 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
24 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
10 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
31 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
26 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 ...
5
votes
1answer
183 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
13 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
46 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
45 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
36 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
237 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
17 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
21 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
13 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
587 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
28 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
68 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
45 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
148 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
42 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
268 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
47 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
56 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
96 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
38 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
56 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
20 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
74 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
33 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
51 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
22 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
42 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 ...
0
votes
1answer
33 views

Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL

I have this statement which generate a random string: SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') FROM ...
0
votes
1answer
46 views

Postgres Pass Subquery results to function that returns TABLE

I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I ...
1
vote
1answer
26 views

ALTER TABLE ADD CONSTRAINT failing when executed from function

So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were. The drop constraints function works ...
0
votes
3answers
39 views

How to prevent trigger from overwriting values that were explicitly set?

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?): create temp table ...
0
votes
2answers
161 views

How to get the current row in postgres function?

I have a table where I have a column called event varchar(255) and user. An event column is a set of strings which tells me in what state was the user when the event was received. I want to count how ...
0
votes
0answers
54 views

Converting User-Defined Datatypes to JSON in Oracle

I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application: CREATE OR REPLACE TYPE udf_typ AS OBJECT ( name VARCHAR2(4000), value VARCHAR2(...
1
vote
1answer
28 views

Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text

I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this: Some code... <div>&nbsp;</div> <div>[flv:http://submarino-libre....
0
votes
0answers
28 views

User-defined harmonic mean function performs worse than query in PostgreSQL 9.6

We created a harmonic mean function based on this blog post: CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric) RETURNS numeric[] LANGUAGE sql STABLE COST 1 AS $h$ SELECT array[$1[1]+...
0
votes
1answer
60 views

How to check and change function volatility in Postgres?

I have a function that I think is defined as volatile, but I now know I want it stable. How can I check the function volatility (volatile, stable, immutable) of a certain function? When a function ...
0
votes
1answer
55 views

Part of parallel execution plan has serial portion

I have a big linq to sql query which runs in parallel mode. It is returning results in 8 seconds and it is pretty good. I noticed in the execution plan that the extreme right part(from where execution ...
0
votes
1answer
26 views

Multiple THEN outputs on a PostgresQL query

Helllo there, I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis. ...
0
votes
0answers
37 views

How to get custom function result in query in MongoDB

I have experience in MySQL and new to MongoDB. What I need to achieve is below. MongoDb collection is like: What I want is a query similar to MySQL as: Select title,binary,hummingDistance(this....
5
votes
1answer
298 views

Why do user defined scalar functions require the schema?

When creating a user defined table function, or a user defined procedure, you can call the function or procedure as named. However, a user defined scalar function requires that you call it with the ...
5
votes
1answer
254 views

Can’t run a function against EXEC

My SQL Server book says you can't run a function inside EXEC and shows an example: DECLARE @NumberOfLetters int = 15; EXEC(‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar) + ‘) AS ShortName ...
1
vote
1answer
90 views

Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL

Hi everyone hope you're doing well I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors . this is my function create or replace function ...