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
107 views

Are the results of the STRING_SPLIT() function returned in a deterministic order?

I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible). For example, take a column definition ...
-1
votes
3answers
203 views

Function: if either x or y are null, return null

I'm looking for an Oracle 18c function that evaluates to null if either of two variables is null. The Constraints: (details here) The function/expression can't contain spaces (so CASE won't work). I ...
1
vote
1answer
49 views

Index on a Computed Column(using udf)

I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for ...
0
votes
1answer
22 views

How do you call an external C function from a SQL stored procedure?

In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure: replace procedure p() sql security creator begin declare l_hash varchar(32); set l_hash = ...
0
votes
2answers
21 views

How to avoid creating and dropping temp tables in functions?

I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs. As far ...
0
votes
2answers
48 views

SELECT sql_function(…) vs SELECT * FROM sql_function(…)

I'm reading the PostgreSQL documentation for the first time and when facing SQL functions I think I have understood all the basics, but still can't see the difference between SELECT sql_function(...) ...
-1
votes
2answers
31 views

PostgreSQL custom generic functions [closed]

is there a way to create a customized generic function in PostgreSQL? I would like to create a custom FLOOR function that accepts two parameters and as a result returns a new column in the query ...
0
votes
2answers
37 views

use of age() function postgres [closed]

I am trying to calculate the time since an event took place. The event timestamp will itself be the result of a MIN aggregate function. Is there a way to do this. I have run a query to get the ...
2
votes
1answer
22 views

Check the result of a math expression in mysql

Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here. a b oper c ------------- 2 3 + 5 4 2 / 3 ...
0
votes
2answers
33 views

Should Postgres function return/coerce numeric scale/precision?

The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function ...
0
votes
2answers
55 views

Debug why Postgres wants to use an uninstalled extension for a function

I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a ...
-2
votes
1answer
49 views

Convert Varchar to Datetime

I have a database field Column_A which is in Varchar(16) not null and the values are for example 20191210215033CS but I want to break it like 2019-12-10 21:50:33 Since it is a production database ...
8
votes
2answers
2k views

Why do we have undocumented and unsupported functions in SQL Server?

After reading an article, dated 2012, about the undocumented functions fn_dblog and fn_dump_dblog I was wondering why they are still undocumented and unsupported if they have been around for a long ...
1
vote
1answer
15 views

How to define a parameter that will be used as an IN argument inside a function?

I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and ...
-1
votes
1answer
102 views

Function that returns table with name passed as a parameter [duplicate]

Trying to create a function that returns a table. I want to pass the table's name as parameter. This is what I tried: create or alter function cust (@T NVARCHAR(250) ) returns table return (select * ...
2
votes
1answer
78 views

Basic alternative to fn_LocalTimeToUTC

My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime. How would I go about getting the same functionality that these two functions offer manually and directly in SQL ...
1
vote
1answer
91 views

Passing parameters for dynamic SQL in a function

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In ...
0
votes
1answer
28 views

Levenshtein function returning different result based on limit value

The Levenshtein function is not working as I would have expect. Is there anything that I minsunderstood? Here is the query: SELECT c0.id, c0.engine_type, c0.mpg, c0.kwh, c0.price, c0.make, c0.model, ...
0
votes
1answer
21 views

If value is null then take the next higher value

I have a function which requires 3 values and then uses these values to return a value from a table. FUNCTION: odwh_data.annual_rental(connection_type,data_rate,date) CREATE OR REPLACE FUNCTION ...
0
votes
1answer
26 views

replace user defined functions (UDF) and tabled value functions(TVF)

Are there techniques to replace UDF tabled value functions with something else? or techniques to make them acceptable? (fast, multithreading and with good estimations)
0
votes
1answer
28 views

Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types?

I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter. A simplified version would look something like this: CREATE OR REPLACE ...
0
votes
1answer
26 views

Give alias / column definition list to ROWS FROM()

This obviously doesn't work: SELECT regexp_matches[1], regexp_matches[1] FROM ROWS FROM ( regexp_matches('fooBarBaz', '[[:upper:]]', 'g'), regexp_matches('fooBarBaz', '[[:lower:]]', 'g') ); Error:...
0
votes
1answer
39 views

Resolve parametrized schema in PostgreSQL

I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function. Why am I looking this way ? This functions will refer %ROWTYPE from multiple schema like Var1 s1....
0
votes
1answer
17 views

Collation conflicts between views and functions when using utf8mb4_unicode_ci collation

Currently running MariaDB 10.3.24 and I really want to fully support the utf8mb4 unicode character set in my databases. The problem I am running into is that when I try and set everything up to use ...
0
votes
1answer
252 views

“invalid transaction termination” in Postgres when trying to update a value using a function

I'm new to postgres. I defined a function like this: CREATE OR REPLACE FUNCTION increment_count() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN UPDATE posts SET count=count+1 WHERE pid=...
0
votes
1answer
52 views

Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures?

If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right? You can call a function inside a procedure create ...
0
votes
0answers
66 views

How to insert variable number of rows inside an SQL Function with multiple queries?

My original idea was to use a transaction, since we have three statements and I want all or none. The transaction block looks something like below: BEGIN; INSERT INTO analysis_record (user_id,...
-1
votes
1answer
34 views

PL/SQL Statment ignored [closed]

I am really new in PL/SQL and I stuck over here. I want to write function which return allProject create or replace FUNCTION GETALLPROJECTS RETURN VARCHAR2 AS currentUserID varchar2(10); BEGIN ...
0
votes
0answers
68 views

parallelism in sql server in user defined function

a four page long query (that creates two temporal tables, and then joins them) in sql server runs in a dozen of seconds (thanks to parallel execution). The same query packed into a user defined ...
0
votes
1answer
72 views

Is it considered good or bad practice to use default values for parameters in stored procedures/functions? [closed]

TL;DR VERSION Is defining default values for parameters in a PostgreSQL function considered good practice, or is it something that is generally frowned upon? Related follow-up question: If/when using ...
0
votes
0answers
503 views

Query performance become slower after upgrade to MySQL 8.0.20

I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20. When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable. But ...
1
vote
1answer
39 views

Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)

I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views. My version required the View to be refactored into a Function as I ...
2
votes
1answer
32 views

In a Postgres function, how to skip expressions if input value is null?

I have a working function. But it only works if I literally supply both the city and country variables. CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text) RETURNS SETOF ...
2
votes
1answer
17 views

Using Postgres param in a function

I'm trying to create a few functions to make the SQL queries between my SQLite and Postgres DB's the same. One thing I would like to do is change a timestamp to a double via a function such as: create ...
1
vote
0answers
44 views

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback. I'd like to ...
0
votes
1answer
94 views

Postgres function/procedure won't return value

I'm new to PostgreSQL, coming from SQL server and I'm having trouble creating a function and/or procedure. First of all, may I just clarify that the only difference between a function and a procedure ...
0
votes
0answers
16 views

Function to return value dependant on seconds

I am trying to create a function to return value from column dependant on seconds. -- Function: ead_credit_within_sla(character, integer, integer) -- DROP FUNCTION ead_credit_within_sla(character, ...
0
votes
0answers
21 views

Will a function which wraps a query criteria be a bottleneck?

I've been attempting to make the following query perform better lately : SELECT BIN_TO_UUID(album_id) AS e, firstdateofinterval AS p, BIN_TO_UUID(territory_id) AS t, SUM(playbacks) AS ...
2
votes
1answer
39 views

Function in same Session become increasingly slow when run multiple times concurrently

PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit Client is Data Grip and same behavior from my report server which uses ...
0
votes
0answers
20 views

How to preform a query as argument in PL/pgSQL function which returns JSON?

I'm trying to perform select by using already created function which returns JSON which aggregates into JSON array, but I'm getting problem in line where I'm performing a SELECT-clause. Tables I have ...
4
votes
1answer
297 views

User-defined function returns first character only although RETURN VARCHAR(10) is defined?

Everyone, I cannot figure out what is missing in the below user-defined function: CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money) RETURNS VARCHAR(10) AS BEGIN IF (@Salary < 30000) ...
2
votes
2answers
306 views

42702 'column reference “id” is ambiguous' for RETURNING of table-returning PL/pgSQL function

This works: CREATE OR REPLACE FUNCTION sql_fun() RETURNS TABLE (id UUID) AS $$ INSERT INTO foo DEFAULT VALUES RETURNING id $$ LANGUAGE SQL; SELECT * FROM sql_fun(); This doesn't: CREATE OR ...
0
votes
2answers
38 views

How to update data with a trigger function and a join

I have two relations : CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int); CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int); INSERT INTO ...
0
votes
0answers
13 views

Function in Postgres that uses the table where I'm passing the function inside the function body?

I've got two tables, one with street_codes and street_numbers (addresses) and another with sidewalks_keys that correspond to each address (address_sidewalks_keys). I would like to create a function in ...
1
vote
3answers
116 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
0
votes
1answer
28 views

How to pass a newly INSERTed row to a function?

I have following tables: table1: table2: | uid | did | | uid | did | |-----+-----| |-----+-----| | 1 | 2 | | '1' | 2 | | 4 | 2 | | '4' | 2 | | 2 | 3 | | '2' | 3 | | 1 | 8 ...
-2
votes
1answer
32 views

Function to convert time difference between two dates into timer format

Does anyone have a function that converts date difference between two dates into timer format ? For example: select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35') The result should ...
1
vote
1answer
51 views

Does “Table Valued Function” force the plan to get into cache?

sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
0
votes
1answer
58 views

relation “table” does not exist

i created the following schema , inside that schema i created a table and inserted a some values. After that i created a function that get a value in the table based on an ID. Here is all the ...
0
votes
1answer
18 views

How to list all functions with arguments that a role is allowed to execute in Postgres?

I found how to list tables a role has permissions on. I found how to list all functions with arguments. My question is: How to list all functions with arguments a role is allowed to execute?

1
2 3 4 5
13