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.
753
questions
-1
votes
2
answers
35
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 ...
- 101
0
votes
2
answers
55
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
19
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
117
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.
...
- 139
1
vote
2
answers
49
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 ...
- 139
0
votes
0
answers
56
views
How to convert a function into a Stored Procedure in Microsoft SQL?
I have this function that takes a decimal parameter and returns a varchar
CREATE FUNCTION dbo.NumberToWords(@Number DECIMAL(18, 2))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Words NVARCHAR(MAX)
SELECT ...
- 33
0
votes
0
answers
34
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 ...
- 137
0
votes
1
answer
58
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 @...
- 33
0
votes
1
answer
88
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
52
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
56
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
206
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('...
- 317
0
votes
1
answer
24
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
64
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
41
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
7
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,967
0
votes
0
answers
90
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
80
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 ...
- 1,960
0
votes
2
answers
165
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
0
answers
31
views
Finde the difference in UPDATE in PostgreSQL
I would like to determine the difference in an UPDATE that was carried out on a table DE_projects. So between old and new values. The aim is to monitor changes in the permissions column in the ...
- 1
0
votes
2
answers
22
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
30
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
154
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
179
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
231
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
41
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 ...
- 33
3
votes
2
answers
1k
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, ...
- 137
0
votes
0
answers
29
views
How would postgres treat a expression that exists in result column and "where" clause at same time?
I coded a function in Postgresql-11, in order to verify whether a column is correct.
Like this:
CREATE TABLE trade_days
(
trade_day date NOT NULL,
previ_day date NOT NULL,
CONSTRAINT ...
- 347
0
votes
1
answer
40
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 ...
- 2,819
1
vote
2
answers
190
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
165
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....
- 347
3
votes
1
answer
2k
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 ...
- 347
3
votes
1
answer
505
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 ...
- 437
8
votes
1
answer
516
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 ...
- 12.9k
4
votes
1
answer
201
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
508
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
977
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, ...
- 143
3
votes
1
answer
2k
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 ...
- 297
1
vote
3
answers
206
views
Alter a scalar UDF that is used in a check constraint in SQL Server 2012
I have a check constraint on a column that uses a scalar UDF. I have a requirement where I need to make changes to the UDF. Based on what I know, I need to drop the constraint, make changes to the UDF,...
- 149
1
vote
2
answers
550
views
PL/pgSQL select statement inside trigger returns null using where NEW
I'm creating a trigger that triggers ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS ...
- 46
1
vote
0
answers
151
views
PostgreSQL - updating a counter outside a loop function
PostgreSQL 14
Here is a function :
CREATE OR REPLACE FUNCTION test(datedebut integer)
returns text
LANGUAGE plpgsql AS
$$
DECLARE
tab integer[];
periodes text;
i integer;
BEGIN
tab := Array[[0,...
- 207
0
votes
1
answer
183
views
Composite data type returned from one postgresql function seems to arrive differently in the caller
I have the following composite data type in a postgres db:
CREATE TYPE IncreaseCredentialsType AS
(
increase_account_id text,
api_key text,
url text
);
I'm returning a value of this type at the ...
- 101
0
votes
1
answer
456
views
MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)
Problem:
you want / need to use dynamic SQL to create procedure / function /
view / trigger in other databases than the current
you can't specify the database name in the statement, CREATE VIEW ...
- 865
0
votes
1
answer
102
views
Is it slower to pass a composite type representing a table to a function then the individual arguments?
Let's say you want to create a function
CREATE FUNCTION f(a,b,c,x,y,z) FROM t;
Is it any slower to restructure the function such that this works,
CREATE FUNCTION f(t) FROM t;
- 63.2k
0
votes
1
answer
95
views
Time series analysis and custom aggregate function for Postgres
I have a table with time series. It contains snapshot and increment value
snapshot means concrete value
increment means take previous snapshot and add increment value to get snapshot value
sensor_id ...
- 137
0
votes
0
answers
30
views
Is calling a stable function performance comparable to reading values from a table?
Let's say we have a function _test_has_group_feature, which is meant to check if a group (identified by a name) has a particular feature (identified by name).
_test_has_group_feature(group_name, ...
- 1,185
3
votes
1
answer
339
views
Error Sql (1064) creating a function in MariaDB
I am getting an error in MariaDB when trying to create a function. It gives me the following error and I leave the script below.
The script:
CREATE PROCEDURE `total_hh`(fechainicio datetime, ...
0
votes
0
answers
518
views
PostgreSQL lower case or remove double quotes on every table name before select query
We are migrating from OracleDB to PostgreSQL and all the tables name in database are lower case while the script from application is mixed with double quotes.
For example the code in application like ...
2
votes
1
answer
514
views
How can I force a system function to use an index in SQL Server?
Good morning team,
I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what ...
- 41