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
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() ...
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... ...

1
2 3 4 5
14