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.
632
questions
0
votes
0answers
24 views
What is the alternative of PL/Java for PostgreSQL 11 and 12?
Understand from: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/11/EDB_Postgres_Advanced_Server_Guide.1.80.html
that PL/Java is deprecated in Advanced ...
2
votes
2answers
141 views
How to create a postgres function that returns a value
I am trying to move some of my web app logic into postgres functions. But I am hitting some errors while creating a very basic insert function.
This is the function that I am trying to create;
CREATE ...
1
vote
2answers
136 views
How can a cheap function in a SELECT make the whole query slow?
I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).
I can't figure out why Query2 below is so much slower than Query1. They ...
0
votes
3answers
30 views
How to return a number surrounded with parenthesis as a negative number
I have a table
CREATE TABLE tab1 (col1 integer, col2 character varying(20));
Col2 contains numeric value but the negative values corresponds with parenthesis not and minus (-). This is the sole ...
0
votes
0answers
32 views
Is there any way I can optimize this merge ranges function?
I have this this function which takes in an array of tsranges, and convert them into one array with tsranges, where each range contains all overlapping ranges from the input.
Version: 12
https://...
0
votes
1answer
30 views
Stream every row coming from a Postgres function
This is the how the function definition looks:
CREATE OR REPLACE FUNCTION my_function(foo NUMERIC)
RETURNS SETOF my_type AS $$
DECLARE rec record;
BEGIN
FOR rec IN SELECT * FROM my_table WHERE ...
1
vote
1answer
19 views
Stored procedure support in MySQL Workbench?
I've created a lot of stored procedures when I was using HeidiSQL on Windows. I am now on an iMAC (x64) and it doesn't look like installing HeidiSQL is an option.
I've got MySQL Workbench version 8.0....
1
vote
1answer
31 views
Using a commit inside plpgsql block works in one case not another
Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...
0
votes
2answers
146 views
How to make a function in postgresql with two parameters to return count from column table?
I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year.
The table is something like this:
CREATE TABLE orders (
id INT NOT ...
1
vote
1answer
29 views
Junction table Trigger after update in another table
I'm just trying to create a trigger that performs an update in a junction table.
In principle, I have three tables with Many-to-Many relationship.
Table a:
a_id, a_is_active
Table b:
b_id, b_is_active
...
1
vote
0answers
20 views
PostgreSQL - Dynamic filter expression
Given a simple function (SQL for the sake of example, can be plpgsql)
CREATE OR REPLACE FUNCTION dynamic_filter(
f INT[] DEFAULT NULL
) RETURN INT[] LANGUAGE SQL AS
$$
SELECT ARRAY_AGG(val)
...
2
votes
1answer
159 views
How to select sub-object with given keys from JSONB?
How to extract / get / select a "sub object" from a json(b) object in Postgres?
There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
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
31 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
52 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
323 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
48 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
87 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
46 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
31 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
28 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
45 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
43 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
61 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
264 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
216 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
66 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
41 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
116 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
69 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
66 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
43 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
27 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
63 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
57 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
72 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
18 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
111 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
123 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
283 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
31 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
48 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
146 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
29 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
65 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
777 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=...
