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.
659
questions
0
votes
0answers
2 views
Why is the result of DATABASEPROPERTYEX changing for AdventureWorks?
I'm using the function DATABASEPROPERTYEX to check the Recovery property of databases, but the result is wrong for the database AdventureWorks (later I noticed the same strange behavior for ...
0
votes
0answers
20 views
Reimplement wm_concat as a user defined aggregate
We have an application running on Oracle 11 that is using wm_concat. We want to migrate it to Oracle 19. As wm_concat is no longer provided in Oracle 19, we considered rewriting all queries using it, ...
0
votes
0answers
23 views
Using Dbeaver, I am getting accessibility-related error when I run some scripts
When I run some scripts on Dbeaver, e.g. calling a function in Postgresql:
SELECT ourSchema.ourCall();
I get the following error:
Dbeaver: An internal error has occurred. Assistive Technology not ...
5
votes
4answers
500 views
Are 'Functional Indexes' Supported in MS SQL Server?
I've just come across expression based indexes (aka "functional indexes") when reading through the documentation for PostgreSQL (I wasn't aware of such indexes previously).
I'm just ...
0
votes
2answers
67 views
SQL Server Try_Cast returns Null value
Related Questions
,TRY_CAST(Question_Text AS xml).value('(/p/text())[1]','nvarchar(1000)') AS Question_Text2
With the help of CharlieFace on my previous post I discovered the use of Try_Cast ...
0
votes
2answers
34 views
Create view with inline function in db<>fiddle
I'm trying to create a view with in inline function in db<>fiddle.
I've removed the CREATE VIEW part for now, and am just trying to run the query, just to get to the root issue.
select /*+ ...
0
votes
0answers
27 views
Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?
Assume A, B, C etc are attribute values in the current rowset
Current function logic is similar to:
Case A = 'Y' then
Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales'
Case B in (99,98,102) OR D ...
6
votes
3answers
435 views
Views versus a function for getting the top records of a table?
I'm talking with a coworker about a way to approach a problem.
We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns Lot and TestGrade.
...
0
votes
0answers
28 views
How to make multiple queries and save the result in a cursor?
I have 4 tables (Company, User, Role, UserRole), I need to get the Id of several users, and then delete them.
TABLE Company
Id
Name
TABLE User
Id
FirstName
Email
Company
TABLE UserRole
UserId
RoleId
...
1
vote
1answer
37 views
Referencing a system function from a DB with a lower compatibility level
I need to be able to parse a string array with STRING_SPLIT on a SQL 2016 instance. The vendor has required the one user DB we have access to be set with a compatibility level of 100 while the system ...
1
vote
0answers
34 views
DRY schema definitions in Postgres?
What is the recommended way to abstract out common code when writing the database schema?
I've multiple tables that have created_at and updated_at columns.
create table if not exists profiles
(
......
0
votes
1answer
37 views
What is the difference between RETURNS `SETOF integer` and RETURNS TABLE(name int)`?
Given the two definitions below, is there any difference?
CREATE FUNCTION foo(OUT foobar int4)
RETURNS SETOF int4
AS 'MODULE_PATHNAME', 'foo'
LANGUAGE C STRICT VOLATILE;
And,
CREATE FUNCTION foo()
...
asked Nov 6 '21 at 19:34
Evan Carroll - Use SO instead
54.3k34 gold badges197 silver badges405 bronze badges
1
vote
2answers
64 views
How are the scalar functions executed by the optimizer
I have read online that scalar functions can affect the performance since the optimizer does not have access to the contents of the scalar function. Since the function is executed for each row, does ...
1
vote
1answer
48 views
Column name as an argument of the function called in the trigger (PostgreSQL)
I looked at all similar questions, but it seems I didn't have enough knowledge to apply them to my issue.
Take a look at the following code. The bottom line is that I have a function that updates the ...
1
vote
1answer
43 views
Variadic functions and composite types
I have a function like so:
update_stuff_variadic(
VARIADIC _stuff_array stuff[]
)
I understand that this syntax requires _stuff_array to match the column structure of the stuff[] table.
Instead ...
0
votes
1answer
52 views
How does the internal function to_char in PostgreSQL work?
I am working in an Oracle to PostgreSQL migration project.
Today I found what I think is a PostgreSQL bug. I wonder whether I missed something in my experimentation.
The following is my test code.
...
5
votes
1answer
197 views
What does BEGIN ATOMIC ... END mean in a PostgreSQL SQL function / procedure?
On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement:
RETURN expression
or a block:
BEGIN ATOMIC
...
0
votes
1answer
90 views
DB<>Fiddle: Custom type and function (ORA-24344)
I've made a custom version of Oracle's GetVertices() function. The solution involves creating a user-defined type and a function.
Here is the code (working):
CREATE TYPE vertex_type_cust AS object
(
...
0
votes
1answer
46 views
How to loop an array and sum values until condition is met?
I have a relational table where I need to loop through and sum amount values until a specified amount criteria is met. The data looks like this:
Company
Account
Amount
Coastal
A384
500
Coastal
C940
...
1
vote
1answer
38 views
PLPGSQL store columns of a 2D query output into array variables
In some step of a plpgsql function I need to store a 2D query result into array variables.
The following code does the job for scalars (but fails with arrays):
SELECT col_a, col_b
FROM my_table
WHERE ...
0
votes
1answer
22 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
41 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
46 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
44 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
52 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
47 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
20 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
25 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
45 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
1k 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
299 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
31 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
33 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
40 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
23 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
129 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
474 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
37 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
32 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)
...
3
votes
1answer
519 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
53 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
79 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
13 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
1k 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
57 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
167 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
53 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
53 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
29 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...
...
