Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
11 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
8 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 ...
4
votes
1answer
183 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
21 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
45 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
39 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
144 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
41 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
253 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
43 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
55 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
59 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 ...
0
votes
2answers
49 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
19 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
34 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
29 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
12 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
20 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
41 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
27 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
39 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
25 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
30 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
78 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
32 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
31 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
53 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
31 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
292 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
253 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 ...
2
votes
1answer
88 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 ...
1
vote
1answer
45 views

In PLPGSQL assign a variable based on result of SQL function

I have a PLPGSQL function which receives an address in two parts: num = '123' location = 'main street, anytown, KS' To utilize the various portions of location I found there is a SQL function ...
0
votes
3answers
68 views

Usage of aggregate function with when self-joining a table

I want to find people from a database who has a weight bigger then the average weight: SELECT * FROM Player p1, Player p2 WHERE p1.weight > avg(p2.weight) But I have an error: Result: misuse of ...
0
votes
1answer
53 views

Error trying a manual's example in DB2 11.1.4.4

When trying this example from the DB2 11.1 manual: VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER)); I get this error: SQL0171N The statement was not processed because the ...
24
votes
1answer
517 views

Self referencing scalar function nesting level exceeded when adding a select

Purpose When trying to create a test example of a self referencing function, one version fails while another one succeeds. The only difference being an added SELECT to the function body resulting in ...
3
votes
1answer
68 views

How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration?

I have a function with 5 parameters, arguments p1, and p2 are mandatory arguments p3, p4, and p5 are optional and have a DEFAULT set The function looks like this, CREATE FUNCTION test_func( p1 ...
3
votes
3answers
5k views

PostgreSQL 11 error: column p.proisagg does not exist

Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error: ERROR: column p.proisagg does not exist LINE 18: ...
0
votes
1answer
72 views

Cannot get a user function to co-operate with a subquery in mariadb

The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing. Notes: Problem ...
2
votes
2answers
110 views

Generic type functions in T-SQL

Consider the following function CREATE FUNCTION dbo.SafeDivide ( @a numeric, @b numeric ) RETURNS numeric AS BEGIN RETURN CASE WHEN @b = 0 THEN 0 ...
0
votes
1answer
39 views

mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF

I do not understand why MySQL is not using the index for the below query. I have a table named users and its create table is this table contains data. Now I am running SQL select statements and it'...
1
vote
1answer
74 views

Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?

I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists). An example ...
10
votes
1answer
226 views

function to receive a character input and return date format (with incorrect input)

I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect ...
9
votes
1answer
252 views

Apparently, my CLR assembly function is causing deadlocks?

Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL ...
10
votes
3answers
341 views

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
0
votes
1answer
30 views

Postgresql table function: get where condition

I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data. Here, there is a very simple example: create or replace function public....