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.
763 questions
1
vote
1
answer
88
views
UDF vs other solutions for checking valid data
I have many 'title' and 'name' columns in different tables that have the same set of restrictions about what characters can be inserted and what not. For example, names should only allow alphabet plus ...
- 121
4
votes
0
answers
297
views
SQL Server 2022 vs 2019 Query Performance gap
I am facing a performance issue with an SQL query.
The query is generated dynamically via the webapp, I am testing with a static version to make things simpler.
The SQL query includes temp tables and ...
- 41
-2
votes
2
answers
120
views
Dayofweek vs weekday [closed]
Why does MariaDb use these terms differently?
#drop table dbstructA;
create table dbstructA(datum date);
insert into dbstructA values('2025-01-01');
insert into dbstructA values('2025-...
- 91
3
votes
1
answer
143
views
Db2 - Upgrade Java JDK/JRE
After installing via alternatives the latest IBM JDK on redhat 8.10 (Ootpa) x86 and when trying to update the Java Version for Db2 with
db2 UPDATE DBM CFG USING jdk_path SDK for Java-path
to
(...) (...
- 33
2
votes
1
answer
102
views
How do I get the value of PI in Db2?
Some DBMSs have a pi() function to give the value of π. I can’t find the equivalent for Db2.
I can use radians(180), I suppose, but is there something more direct?
- 3,207
1
vote
1
answer
109
views
How do I make this sql function inlineable in PostgreSQL 17.2? [duplicate]
When I use the following stable sql function in my queries, instead of its body directly, the execution is significantly slowed down. From the wiki I read this about scalar functions:
the body ...
- 11
0
votes
1
answer
83
views
DB2 LUW SQLCODE=-740, SQLSTATE=51034 on UPDATE with function modifies sql data
I have a user-defined function that modifies sql data. It inserts into one table and returns the ID of the returned row. Trying to use it like this
UPDATE table_X SET REV = REVISION(:timestamp, :user) ...
- 123
0
votes
0
answers
22
views
What are some example workflows for automatic loading of Foreign tables into tables each month?
I have a PostgresDB and PgAdmin4 installed. Beginning of each month a database with monthly payments is loaded as a Foreign table payments and a data for monthly customer numbers is loaded in Foreign ...
- 1
3
votes
1
answer
122
views
Permission denied for index idx_my_index on using pg_prewarm
I work on a project that uses PostgreSQL. Some analytic queries take a lot of time, and we would like to prewarm two indexes and one table. Here are the prewarming queries:
SELECT pg_prewarm('...
1
vote
1
answer
120
views
MySQL: using NAME_CONST internally
I'm using MySQL 8.0.32
I have some questions about an internal conversion, that I don't understand. In addition to this, sometimes this conversion is shown in SHOW FULL PROCESSLIST, as its execution ...
- 155
0
votes
1
answer
101
views
Custom Function Filters
I have a function that currently uses 3 parameters:
CREATE FUNCTION dbo.RatesReport (@StartTapeDate DATE, @StartFcbVersionId int, @StartSvbVersionId int)
SELECT *
FROM dbo.DataTapes t
WHERE t.TapeDate ...
- 27
0
votes
0
answers
88
views
PostgreSQL variadic array of arrays of composite type
I want to write a function that accepts array of arrays of my composite type. because of they have different lengths, it can NOT be defined as one array. So, I want to use variadic to accept arrays ...
2
votes
1
answer
275
views
Using a function as argument to GRANT fails with syntax error
I can't get GRANT to take current_database() as argument. These all fail with the same error:
GRANT ALL PRIVILEGES ON DATABASE current_database() to justintestin;
GRANT ALL PRIVILEGES ON DATABASE (...
- 131
1
vote
1
answer
37
views
Using custom table definitions in a multi statment table function
I have a custom type and a table, and I want to reuse this as a return result for several table functions. I want to declare the type rather than recreate the definition. So the start of the function ...
- 11
-1
votes
2
answers
351
views
LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?
I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
- 143
0
votes
2
answers
402
views
Function performance is worse than raw query in PostgreSQL
I am trying to squeeze out the all the performance from my PostgreSQL database, also I want to abstract my query definitions from my application layer.
To do that, I am using table-valued function. ...
- 3
1
vote
1
answer
103
views
SQLite: How to include an Sqlean define statement in a `.sql` file?
I need to generate an SQL file containing UDF defined with the extension 'define' of Sqlean.
Here is a minimum non-working dump.sql file:
SELECT load_extension('path/to/sqlean/define');
SELECT define('...
- 121
0
votes
1
answer
341
views
How can functions that make heavy use of table variables be optimised?
Suppose that you have a multi-statement table-valued function that makes heavy use of table variables. What methods exist for optimising them? I only know of the following and they all disappoint me.
...
- 1,360
1
vote
2
answers
101
views
Query to return latest record between 2 tables
I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data:
create table customer (
cusID char(5),
cusPurDt ...
- 75
4
votes
2
answers
3k
views
Are views logically redundant?
Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any ...
- 1,360
0
votes
0
answers
73
views
In a scalar function on SQL Server, is the only form of flow control via If...Else?
In a scalar valued user-defined function on SQL Server 2019, is the only form of flow control the If...Else block?
Ideally I would rather use CASE...END, but everything I've found indicates this is ...
- 147
0
votes
1
answer
351
views
Convert numbers to words | Spell out DECIMAL USD 1259.75 as 'One thousand two hundred fifty-nine dollars and seventy-five cents'
ALTER FUNCTION [dbo].[NumberToWords](@Number DECIMAL(18, 2))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)
SELECT @Words =
COALESCE(@Words + ' ', '') +
CASE
WHEN @...
- 25
0
votes
1
answer
306
views
EVENTDATA() function is giving only null values in sql server
I'm trying to capture logins time with a trigger using EVENTDATA() function.
But It's giving Null values only for all types of events. Can anyone please help me on this?
This is my Trigger
CREATE ...
- 1
0
votes
1
answer
70
views
How do I save a function that I want to frequently use?
Due to the nature of how I ingest data into my SQL database it's prone to duplicates so I have the following view:
SELECT IP, COUNT(*) AS Count
FROM ipdb
GROUP BY IP
HAVING COUNT(*) > 1;
Current ...
- 101
0
votes
1
answer
92
views
Split lines against polygons in a PostGIS trigger
I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
- 1
2
votes
1
answer
231
views
How to define a function accepting exactly three characters?
I tried this:
create or replace function c3 (arg char(3))
returns void
language plpgsql as
$$
begin
raise notice 'Value: %', arg;
end;
$$;
But it accepts more than three characters:
select c3('...
- 379
0
votes
1
answer
30
views
Inversion of the string register
I'm using PostgreSQL and I have a table with serial numbers.
The serial number is like this
The serial number is like this zHOb55Klast,
I want to make it like this ZhoB55kLAST.
How can I do that?
- 3
1
vote
1
answer
152
views
Postgresql near real-time
Before asking the question I will update you on the situation I face.
I have physical tables in postgresql that are continuously updated by various external flows (.NET, Java, etc...), at the moment ...
0
votes
1
answer
160
views
Why is PostgreSQL `coalesce` not working as expected/hoped inside a function? [closed]
A simple function that looks like this:
CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text)
RETURNS tsvector
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
RETURN
to_tsvector(...
0
votes
1
answer
48
views
How can I get a linear forecast in BigQuery?
BigQuery has no native support for linear forecasting outside of machine learning, but it does support user-defined functions, and these can accept arrays as inputs. However, (as of 2023/10 anyway), ...
- 5,985
0
votes
0
answers
93
views
Scalar udf abysmal performance
I have a udf. It is essentially a case statement, using ifs instead and multiple "broken" assignments of the same variable, see code below.
It does not access any table. Still, on one of my ...
- 447
0
votes
1
answer
122
views
Scalar Functions Causing Plan Cache Bloat
Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below:
SELECT dbo.fnSomeFunction('12345678')
I have done some testing and found that ...
- 2,256
0
votes
2
answers
366
views
About version control on the programming scripts of SQL Server, e.g., stored procedures and functions
We have an instance of SQL Server (MSSQL) version 2016, and the database contains programming scripts, e.g., stored procedures and functions.
For the programming scripts of MSSQL, we wonder how to do ...
- 149
0
votes
2
answers
81
views
Compare dev/prod databases functions
I have two databases - dev and prod. I modify logic and debug in dev then pull everything to prod. A significant part of my app are PostgreSQL PL/pgSQL functions. I write them in dev, test, make sure ...
- 101
0
votes
1
answer
47
views
PG not using index in queries with functions: `in (select some_func())`
I have a select with sub-select in where:
select * from injections where id in([some select]);
It uses an index and everything is fine. But now when I move the sub-select statement into a function ...
2
votes
0
answers
378
views
Oracle 19C Auto Compile fails on Package With SQL_Macro but ALTER PACKAGE ... COMPILE Succeeds
We have a package SCHEMA.MYPACKAGE with a procedure SCHEMA.MYPACKAGE.GETDATA that calls a sql macro SCHEMA.SQLMACROFUNCTION that exists within the same schema but outside the package. The macro is ...
- 31
0
votes
1
answer
378
views
Json filling in trigger
Good afternoon There is a label in which records are added. Each entry has a json column. Json has several 6 fields. When adding a new record, the data from it may refer to the old record. It is ...
- 1
0
votes
1
answer
595
views
ROW_NUMBER() with OUTER APPLY
I have a query that returns the lowest Price of a ProductVariation by using ROW_NUMBER() to enumerate the rows in groupings (partitions) of the ProductId, sorted by the Price ascending (MIN function ...
- 135
0
votes
2
answers
93
views
MySQL Stored Function - How to get @@warning_count?
I have the following MySQL 8.0 stored function:
DELIMITER $$
CREATE FUNCTION maybe_utf8_decode(str text charset utf8mb4, rowid INT)
RETURNS text CHARSET utf8mb4 DETERMINISTIC
BEGIN
declare ...
- 45
4
votes
2
answers
2k
views
Function returns one row, but the query it wraps returns many
A very odd situation, where a query returns 1380 items, but when wrapped in a function, then same function returns a single row
I've tried deleting and recreating the function.
When called like this, ...
- 147
0
votes
1
answer
80
views
Separating out the standard functions in PostgreSQL
I’ve been using PostgreSQL for many years, and, among other things, I have added a number of my own user defined scalar functions.
Of course, if I knew about it at the time, I should have added my own ...
- 3,207
1
vote
2
answers
329
views
MySQL function returns wrong value on repeat, when it contains window functions
MySQL 8.0.33
I have a stored function to calculate the sum of all time gaps in a series of events on a given day. This function uses a window function LEAD(...) OVER() to determine the gap between the ...
- 13
0
votes
1
answer
330
views
How to return DDL success in a plpgsql function?
I have hundreds tables in a Postgresql-11 DB, need to be droped.
In order to simplified my work, I coded a function as following:
CREATE OR REPLACE FUNCTION dropTable(
IN tab_name information_schema....
- 413
4
votes
1
answer
5k
views
How to make a Postgresql function parallelly running?
I have thousands tables with same structure, and I need to add a index on each.
In order to simplify my work, I code a function like this:
CREATE OR REPLACE FUNCTION createIndexTD(
IN tbl_name ...
- 413
3
votes
1
answer
831
views
Is this function PARALLEL SAFE?
Looking at the function f_jsonb_select_keys from this answer:
How to select sub-object with given keys from JSONB?
https://www.postgresql.org/docs/current/functions-aggregate.html
Aggregate functions ...
- 487
8
votes
1
answer
677
views
Schema-binding with recursive scalar UDF
TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes ...
- 18.1k
4
votes
1
answer
378
views
Why does my Postgres function always return the same value, regardless of input?
I have two tables in a Postgres 15 database:
CREATE TABLE apps (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
created_at timestamp with time zone NOT NULL DEFAULT now(),
...
- 143
0
votes
1
answer
703
views
Resultset from jsonb array
I have a function that build a JSONB array of object.
CREATE OR REPLACE FUNCTION my_func()
RETURNS TABLE(field1 INT, field2 TEXT)
In DECLARE part:
DECLARE
r RECORD;
d JSONB;
d_list ...
- 67
2
votes
1
answer
2k
views
In Postgres, is an unprivileged role able to execute a function with elevated privileges?
For context, I am using:
postgREST: a thin REST client on top of Postgres (which does all the heavy lifting)
Azure AD: which handles identity and access of the organization
Since users, roles, ...
- 153
3
votes
1
answer
3k
views
ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function
I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.
There is any ...
- 307