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.
611
questions
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?
