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

Error creating FUNCTION [closed]

I am trying to create a FUNCTION. The sql returns CREATE FUNCTION. Any pointers why it doesn't create the function, would be appreciated? The SQL is: CREATE OR REPLACE FUNCTION update_symbol_changes() ...
user avatar
0 votes
1 answer
19 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
28 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
29 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
19 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
50 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
56 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
  • 407
1 vote
1 answer
46 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
44 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
30 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
106 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
51 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
56 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
18 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
180 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
126 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
0 votes
1 answer
849 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
  • 393
0 votes
0 answers
29 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.4k
0 votes
1 answer
44 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
56 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
0 votes
1 answer
71 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,394
0 votes
0 answers
70 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
533 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
86 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
91 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
451 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
44 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
48 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
40 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
97 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.2k
1 vote
2 answers
89 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
190 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
72 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
101 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
7 votes
1 answer
1k 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,087
0 votes
1 answer
147 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
117 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
71 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
28 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
153 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
-3 votes
1 answer
93 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), ...
user avatar
0 votes
1 answer
95 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', '...
user avatar
0 votes
2 answers
73 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 ...
user avatar
  • 9
0 votes
1 answer
71 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,...
user avatar
0 votes
0 answers
31 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 ...
user avatar

1
2 3 4 5
14