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
0
votes
1answer
16 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
21 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
24 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
34 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
16 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
53 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
49 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
59 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
33 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
41 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
42 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
78 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
36 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
28 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
15 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
33 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
34 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
37 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
18 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
269 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
111 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
35 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
80 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
24 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
30 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
46 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
51 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
15 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?
34
votes
1answer
2k views

SQL Server 2019 executes unreachable code

[Update: This question describes a bug which has been fixed in Cumulative Update 5 for SQL Server 2019.] Consider the following repro example (fiddle): CREATE FUNCTION dbo.Repro (@myYear int) RETURNS ...
2
votes
0answers
46 views

Why does sql server consider a function that extracts a value from an xml parameter non-deterministic?

I want to add a computed field that extracts a value from another xml column. The function looks like this: create or alter function extractValue(@val xml) returns varchar(14) as begin return @val....
1
vote
3answers
30 views

Tabled Value Function returns incorrect or no results

I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well. Here is my code: USE [RMC_Tracker] GO /****** Object: ...
1
vote
2answers
17 views

Insert to a table using function with default arguments

I'm trying to write a function that makes an insert to the table defined below: DROP TABLE IF EXISTS pl_table; CREATE TABLE pl_table ( id SERIAL PRIMARY KEY, mandatory VARCHAR NOT ...
3
votes
1answer
49 views

generating all substrings (n-grams) for bit-strings

I followed SELECT all substrings (n-grams) of length n? to get a function for generating all n-grams for text types. Works great. I figured out how to cast my bit-strings to text, and the function ...
0
votes
0answers
8 views

New dataframe for different groups

I have a dataframe with a column 'Country'. Every country has multiple records. I want to write a function in python using pandas so that for every country it can return a seperate dataframe. Having ...
4
votes
1answer
43 views

Generate due dates using function in a select statement

I want to create a semi - monthly amortization schedule by using a function. Here is my function: CASE WHEN day(@dt) < 16 THEN CASE WHEN month(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF ...
0
votes
2answers
45 views

How use raise notice in nested and sub-function?

I'm using PgAdmin to create multiple pgsql functions. I'm beginner on this environment. I'm using 'raise notice' to help me for debugging phase. I have two functions with 'raise notice' statement. ...
0
votes
0answers
9 views

Function returning a table has open parenthesis and single quote mark in results

I have a function returning a table and in the results I have weird results. Result should be (A,somereturnedtextvalue), what I get is (A,"somreturnedtextvalue There is no closing quote or ...
0
votes
1answer
20 views

Need a function to run different queries based on parameters sent subquery must return one column error

I am new to advanced sql programming. I am storing constants. Constants have three possible scopes: local, user and global. Two tables: s0constants_user holds set values of constants s0constants ...
0
votes
2answers
35 views

How to sort aggregate function call

If I have an expensive function call that takes a JSONB and returns an INT, e.g. CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS How do I go about ordering the aggregate result ...
0
votes
2answers
38 views

Trouble when accessing old after deleting a row in postgrsql

When I delete an athlete from my database I want to take the sum he has raised and divide the sum between all the other athletes in my database. When I run my code "addBalance" becomes Null and ...
6
votes
1answer
245 views

Select permission denied when using linked server in a function unless inlining is off

We are encountering an unusual error in our beta environment after upgrading to SQL Server 2019. This occurs when selecting from a scalar-valued function. This error does not occur in our production ...
0
votes
1answer
48 views

Does setting Query Optimizer Fixes to ON enable Scalar UDF Inlining on a database with compatibility level 140?

Azure SQL Database Managed Instance. Current Compatibility Level is set to 140 to match on-prem SQL Server 2017 test and development server. I just read this article on Scalar UDF Inlining and was ...
1
vote
1answer
61 views

Update record with converted epoch timestamp from another column

I'm trying to convert epoch data in one column (timestamp_epoch) to a legitimate timestamp in another column (timestamp) for every record in a table. In reading the PostgreSQL Documentation it ...
1
vote
1answer
90 views

character varying vs. text as function parameter types in PostgreSQL

Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples: Example 1: CREATE OR REPLACE ...
4
votes
1answer
272 views

Changing to compatibility level 150 makes SQL Server consume all available memory

We're moving an existing DB to a new SQL Server 2019 instance. If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available ...
0
votes
0answers
15 views

Create postgresql function to be triggered before insert row

I've created a function to be executed before inserting rows in a table, in order to avoid the error "malformed array literal". It still throws the error and I deduct that the process starts earlier ...
2
votes
1answer
1k views

Postgres get function owner

I would like to be able to get a table of function names and function owner names. Currently, this query returns a list of functions for a given schema: SELECT proname FROM pg_catalog....

1
2 3 4 5
12