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.
639
questions
0
votes
1answer
13 views
Postgres preventing update on "events" table but insert "warning" event
I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database.
My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
0
votes
1answer
28 views
How to reuse list of columns in multiple statements
I have a list of columns for each entity I want to get from a SELECT/UPDATE/INSERT statement.
I want to reuse the same list instead of having a copy in each query.
Example:
SELECT col1, col2 FROM ...
0
votes
1answer
42 views
Looking for help understanding what is happening in this SQL Script that shows key info about SQL Server Autogrowth Events
Here is the code below:
DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
SELECT @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;...
-3
votes
1answer
30 views
Get datetime based on change in another column using UDF and computed column in TSQL [closed]
Given: Given a Microsoft SQL database table Log with multiple columns including these important ones: id (primary key), code (an integer that can take multiple values representing status changes), ...
0
votes
1answer
43 views
Understanding Postgres's similarity algorithm
Could someone explain how Postgres's similarity algorithm works?
Why do these examples return different results?
select similarity('Foo Bar Baz', 'Foo Bar'); -- 0.8
select similarity('Foo Bar Baz', '...
0
votes
2answers
44 views
Oracle Lag() on date output wrong data [closed]
Oracle DB 19 - Sql developper 19 - NLS DD-MON-RR
I have a table
ID NAME EFFDT NODE PARENT
SHA DEPT 01-JAN-01 A10200 TOP
SHA DEPT 04-JAN-11 A10200 X10200
SHA ...
0
votes
1answer
11 views
How to interpolate a function return of column names into the SELECT section of a QUERY [MYSQL]
I deal with tables with many columns - recently I realised I can generate a list of column names excluding one column (maybe PK), that I don't want.
Like so:
mysql> SET @notColumns = 'primary_key,...
0
votes
0answers
24 views
Function not working in postgresql 13
We have below function which needs to check some conditions before accepting the password from user input.
Here we are first accepting the user input through function call.
First it will check ...
0
votes
0answers
25 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
379 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
173 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
33 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
20 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
36 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
225 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
30 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
22 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
222 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
33 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
54 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
10 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
628 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
50 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
108 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
48 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
38 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
83 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
44 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
80 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
323 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
217 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
69 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
42 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
173 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
81 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
73 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
44 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
29 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
74 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
58 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
76 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
113 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
138 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
383 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 ...
