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
-1 votes
2 answers
36 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 ...
Björn Morén's user avatar
0 votes
2 answers
57 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. ...
vurqac's user avatar
  • 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('...
Aristide's user avatar
  • 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. ...
J. Mini's user avatar
  • 139
1 vote
2 answers
50 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 ...
tkmagnet's user avatar
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 ...
J. Mini's user avatar
  • 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 ...
Luis Avalos's user avatar
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 ...
spinjector's user avatar
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 @...
Luis Avalos's user avatar
0 votes
1 answer
89 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 ...
dbajayy's user avatar
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 ...
skarz's user avatar
  • 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 ...
idrizza's user avatar
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('...
ceving's user avatar
  • 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?
JavaTech's user avatar
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 ...
Luis Di Matteo's user avatar
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(...
Kasbolat Kumakhov's user avatar
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), ...
Jon of All Trades's user avatar
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 ...
George Menoutis's user avatar
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 ...
SE1986's user avatar
  • 1,960
0 votes
2 answers
168 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 ...
James's user avatar
  • 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 ...
MTwain's user avatar
  • 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 ...
Sergei Basharov's user avatar
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 ...
Stanislav Bashkyrtsev's user avatar
2 votes
0 answers
155 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 ...
lightwing's user avatar
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 ...
Ivan's user avatar
  • 1
0 votes
1 answer
232 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 ...
gregoryp's user avatar
  • 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 ...
peppy's user avatar
  • 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, ...
New Alexandria's user avatar
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 ...
Leon's user avatar
  • 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 ...
Manngo's user avatar
  • 2,819
1 vote
2 answers
191 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 ...
Dmitriy's user avatar
  • 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....
Leon's user avatar
  • 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 ...
Leon's user avatar
  • 347
3 votes
1 answer
508 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 ...
jian's user avatar
  • 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 ...
Charlieface's user avatar
4 votes
1 answer
203 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(), ...
Dancrumb's user avatar
  • 143
0 votes
1 answer
509 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 ...
ceadreak's user avatar
2 votes
1 answer
981 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, ...
dnk8n's user avatar
  • 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 ...
Héctor's user avatar
  • 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,...
RobMartin's user avatar
  • 149
1 vote
2 answers
552 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 ...
Moifek Maiza's user avatar
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,...
Leehan's user avatar
  • 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 ...
cayblood's user avatar
  • 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 ...
Thomas Franz's user avatar
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;
Evan Carroll's user avatar
  • 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 ...
Capacytron's user avatar
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, ...
Gajus's user avatar
  • 1,185
3 votes
1 answer
341 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, ...
Eduardo Estay Atenas's user avatar
0 votes
0 answers
520 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 ...
Entaah Laah's user avatar
2 votes
1 answer
518 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 ...
Zoe's user avatar
  • 41

1
2 3 4 5
16