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.
546
questions
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> </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 ...
