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
1 vote
0 answers
26 views

Clever upserting and deleting

Let's say I have a table that has roughly the following schema (for brevity, I only kept the relevant fields): CREATE TABLE IF NOT EXISTS some_table ( id SERIAL NOT ...
user avatar
1 vote
1 answer
19 views

Define a function on the fly as with CTE in MySQL

With CTE it is possible to define 'on he fly' custom tables that exists only during the execution of the current query: is it possible to do the same for functions? Example use case I have a query ...
user avatar
  • 111
4 votes
1 answer
108 views

How to pass the current row to Generated Column function in Postgres?

Postgres 12+ supports generated columns: https://www.postgresql.org/docs/current/ddl-generated-columns.html From the docs, the syntax seems limited - it forces one to explicitly name the columns on ...
user avatar
0 votes
2 answers
30 views

Why are scalar functions special? A case study of trying to understand database terminology as a beginner

I am trying to learn the basics of databases, but I am finding the terminology especially impenetrable compared to other things I have learned. Most of the database tutorials and documentation I have ...
user avatar
11 votes
2 answers
619 views

Why Does SQL Server Track Time Accurately For Some Multi-Statement Table-Valued Function Query Plans, And Not Others?

Setup For this demo, I'm using the 2013 version of the Stack Overflow database, and SQL Server 2022 CTP2, but it is valid going back through SQL Server 2017 which is as far back as I care to check. ...
user avatar
  • 34.2k
0 votes
1 answer
19 views

How Can I Write a Postgres "if" Based on a SELECT?

If I have a function in PostgreSQL, I can validate its arguments (eg. a username argument) like so: IF LENGTH(username) < 4 THEN RAISE EXCEPTION 'Usernames must contain at least four characters'...
user avatar
1 vote
0 answers
25 views

How is text and number giving me same output with RIGHT function in MySQL?

This is SALESMAN table in MySQL: Scode Sname Area Qtysold Dateofjoin S001 Ravi North 120 2015-10-01 S002 Sandeep South 105 2012-08-01 S003 Sunil NULL 68 2018-02-01 S004 Subh West 280 2010-04-01 ...
user avatar
0 votes
2 answers
34 views

Force stable postgres function to be executed once

I've created a naming function for multiple table id combinations for human understanding. Essentially it denormalises tables combining the important data into a varchar. It takes 3 parameters, and is ...
user avatar
  • 101
0 votes
0 answers
13 views

Question with ADD MEMBER FUNCTION in Oracle SQL Developer

Im trying to do a function where it return the advisors of the campus clubs, and their respective clubs names as "varchar". But I did not understand very well the parameters that should fill ...
user avatar
0 votes
1 answer
29 views

How to create a function that would be available for all schemas?

How can we create a function in a PostgreSQL database so it would be available for all schemas? Also, is there a way to create a function that would be available for all databases and all schemas as ...
user avatar
  • 101
0 votes
1 answer
32 views

PostgreSQL triggered update function lock mechanism

I'm fairly new to database administration and PostgreSQL, and there's some things I'm not familiar with, even tho I've read a lot of the PostgreSQL documentation. Here is a simplified version of the ...
user avatar
0 votes
1 answer
83 views

Speed up Postgres Query and Function

I have a tables that looks like this: create_table "cboe_raws", force: :cascade do |t| t.date "quote_date", null: false t.string "underlying_symbol", null: false t.string ...
user avatar
0 votes
1 answer
22 views

Calculate difference based on timestamp

in my PostgreSQL DB I have got table like that: calendar_tab calendar_id - int user_id - int type - varchar(16) start_date - timestamp end_date - timestamp Users add different events to this table. ...
user avatar
  • 101
0 votes
0 answers
55 views

PostgreSQL- Grouping results without using GROUP BY

I'm trying to understand the logic behind the group by and the possible alternatives to it. That's why, I've been trying to solve a puzzle where I try to make the same as with a group by clause but ...
user avatar
0 votes
2 answers
59 views

UDF and built-in function behavior

Take the following queries: DECLARE @X VARCHAR(200) = '1,2,3,4' SELECT *, dbo.aUserDefinedScalarFunction(4) AS ScalarValue FROM MyTable T INNER JOIN dbo.aUserDefineTableFunction(@X) A ...
user avatar
  • 417
1 vote
1 answer
69 views

Splitting up a few big tables into multiple ones and avoiding empty tables

I am in the process of restructurizing and unifying a large database in PostgreSQL. One part of this is to split up some large tables into smaller ones according to their value in 'key'. The function ...
user avatar
0 votes
0 answers
69 views

PostgreSQL cache function return value

Suppose I have defined a function that is expensive to compute. Is there a way to define an index in PostgreSQL to cache the return value for a given set of parameters? For example, let's say that I ...
user avatar
1 vote
1 answer
33 views

Get explain plan for query with inline function

I have a query that has an inline function: with function with_f(p_text in varchar2) return varchar2 is begin return p_text; end; select with_f(dummy) from dual Is there a way to get the ...
user avatar
  • 1,387
0 votes
1 answer
197 views

Time zone lost when returning timestamp with time zone from a function

I have this function: CREATE OR REPLACE FUNCTION laborable_day(dtDateTime TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP WITH TIME ZONE AS $$ DECLARE _isHoliday BOOLEAN; BEGIN LOOP SELECT COUNT(*...
user avatar
1 vote
2 answers
67 views

How to find data type of output in MySQL?

I was reading about the CONCAT() function, and I noticed this: mysql> SELECT CONCAT(14.3); -> '14.3' While running this command in MySQL 8.0 Command Line Client, the output didn't have ...
user avatar
0 votes
2 answers
74 views

I'm trying to make an SQL injection in my own function in PostgreSQL 13

Just for learning purposes, I'm trying to create a function using PLPGSQL and make an SQL injection on it. I recently learned about format, USING and quote_literal and quote_indent, so I'm good about ...
user avatar
1 vote
0 answers
20 views

How to return query result to a tset and later process the data?

Table and values are as follows: -- create a table CREATE TABLE table ( id INTEGER PRIMARY KEY, a integer[], b integer[] ); -- insert some values INSERT INTO table VALUES (1, '{1,2,3,4,5,6,7,8,9,...
user avatar
0 votes
1 answer
304 views

Dynamic CREATE TABLE AS with indexes

I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process). One of the things I need to move are a bunch of stored procedures, that generate tables ...
user avatar
1 vote
0 answers
321 views

Postgresql trigger insert on another table after update and update other tables

I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger. I have an intermediate table deleted_post where I insert the ...
user avatar
1 vote
1 answer
2k views

Postgres function for NVL

I'm trying to have NVL function in postgres. create or replace function nvl (anyelement, anyelement) returns anyelement language sql as $$ select coalesce(cast( $1 as decimal), cast( $2 as decimal)...
user avatar
  • 425
0 votes
0 answers
32 views

why does external java function terminate recursive cte?

I made a peculiar observation regarding external functions written in java which I hope someone can shed a light on. I tried it with several functions, but I'll use one of them as an example here: ...
user avatar
  • 21.7k
0 votes
1 answer
52 views

Filter rows using a probabilistic filter (bloom filter or cuckoo filter) in Postgres

I have a large table (~1B rows) with a btree index on a single bytea column (~20GB, but fits in memory). I'd like to send a query from the client with a probabilistic filter (e.g. a bloom filter or a ...
user avatar
0 votes
1 answer
67 views

Immutable dynamic sql functions

Scenario #1 Basically, lets say we have a function that's using dynamic SQL but, also know for a fact that it will always return the same output for the same input and declare it immutable will it ...
user avatar
  • 1,074
0 votes
1 answer
79 views

Why is the result of DATABASEPROPERTYEX not consistent 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 ...
user avatar
  • 4,589
0 votes
0 answers
104 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, ...
user avatar
5 votes
4 answers
549 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 ...
user avatar
  • 171
0 votes
2 answers
98 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 ...
user avatar
0 votes
2 answers
104 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 /*+ ...
user avatar
  • 1,387
0 votes
0 answers
28 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 ...
user avatar
6 votes
3 answers
469 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. ...
user avatar
0 votes
0 answers
51 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 ...
user avatar
  • 101
1 vote
1 answer
51 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 ...
user avatar
  • 1,913
1 vote
0 answers
60 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 ( ......
user avatar
0 votes
1 answer
150 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() ...
user avatar
  • 56.7k
1 vote
2 answers
107 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 ...
user avatar
1 vote
1 answer
269 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 ...
user avatar
1 vote
1 answer
92 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 ...
user avatar
0 votes
1 answer
115 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. ...
user avatar
10 votes
1 answer
2k 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 ...
user avatar
  • 1,127
0 votes
1 answer
157 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 ( ...
user avatar
  • 1,387
0 votes
1 answer
158 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 ...
user avatar
1 vote
1 answer
129 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 ...
user avatar
  • 181
0 votes
1 answer
36 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 ...
user avatar
  • 101
0 votes
1 answer
197 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 ...
user avatar
  • 126
0 votes
1 answer
49 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;...
user avatar

1
2 3 4 5
14