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.
622
questions
6
votes
2answers
1k views
How can I eliminate this scalar function or make it faster?
I'm working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql ...
0
votes
2answers
27 views
Add a column with name as a result from a SELECT
I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table.
...
1
vote
1answer
41 views
Function to compute difference between 2 times
I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I ...
0
votes
1answer
8 views
Problem calling Postgres function
I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, ...
0
votes
0answers
17 views
How can I auto-create a partition in a declarative partitioned table in Postgres when a newly created row has no matching partition?
My goal here is to make a partitioned table that's simplified below
Group
Info
A
blah1
B
blah2
but instead of having that as one table, I want to partition my table per group so that there's a ...
0
votes
2answers
35 views
Error when passing variable to function
I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function:
CREATE OR REPLACE FUNCTION public.intercept_ddl()
RETURNS ...
0
votes
1answer
30 views
Perform update and raise notice in Postgres Function
I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened.
I don'...
1
vote
0answers
41 views
MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?
TL;DR;
I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
0
votes
2answers
26 views
PostgreSQL - Functions-Body-Security
I'm thinking about how to hide as many attributes of relations from a user as possible & if functions can help with that.
Example:
The user "dummy_user" should have as little insight as ...
0
votes
1answer
25 views
Database function SELECT with optional argument [closed]
I need to perform a SELECT in postgres that filters records only when a certain argument value is not null. If the argument is null then the argument is ignored. I am experimenting with a function...
...
0
votes
1answer
28 views
Pass variable as an input argument to a function inside another function in PostgreSQL
I am new to PostgresQL and having some trouble in feeding a function with a variable from an outer function. (maybe it is not possible...)
Inside a function I am trying to assign a value from calling ...
0
votes
1answer
38 views
Calculating available inventory for a perishable good
Long time reader but first post!
I am designing a database that requires me to be able to calculate the inventory available for a perishable good on any given day. In my case, it is for a farm. ...
-1
votes
2answers
49 views
How to create an index based on a time-based function in postgres? [closed]
I have a function called hot_rank to rank posts, based on their score, and their newest_comment_time (basically a decaying power function):
create or replace function hot_rank(
score numeric,
...
5
votes
1answer
160 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
214 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
60 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
30 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
31 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
59 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
45 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
39 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
23 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
38 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
56 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
57 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
105 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
94 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
183 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
29 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
32 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
63 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
46 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
22 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
483 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
61 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
84 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
35 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
106 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
152 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
963 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
45 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
64 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 ...
3
votes
1answer
18 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
117 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
186 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, ...
