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
1
vote
1answer
17 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
25 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 ...
0
votes
0answers
24 views

PostgreSQL call a function in a function

I have 2 tables. One trigger on each. On tbl2 : ON INSERT, trg2 takes a value from a column named "z" in tbl1 and do some maths (in tbl2). On tbl1 : ON UPDATE, if a value of "z" has changed, I would ...
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
16 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
35 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
17 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
32 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
25 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
36 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
30 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
2answers
235 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
14 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
48 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
48 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
37 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
250 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
22 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
24 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
14 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
614 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
87 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
149 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
43 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
290 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
48 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
64 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
117 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
43 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
65 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
101 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
36 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
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
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
54 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
52 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
28 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
42 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
195 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
65 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]+...

1 2 3 4 5 12