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.
679
questions
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
...
- 127
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 ...
- 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 ...
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() ...
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 ...
- 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 ...
- 103
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 ...
- 1
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.
...
- 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 ...
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 ...
- 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 ...
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 ...
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 ...
- 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(*...
- 117
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 ...
- 127
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 ...
- 126
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,...
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 ...
- 35
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 ...
- 111
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)...
- 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:
...
- 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 ...
- 145
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 ...
- 959
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 ...
- 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, ...
- 101
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 ...
- 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 ...
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 /*+ ...
- 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 ...
- 257
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.
...
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
...
- 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 ...
- 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
(
......
- 123
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()
...
- 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 ...
- 53
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 ...
- 13
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 ...
- 27
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.
...
- 41
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
...
- 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
(
...
- 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
...
- 1
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 ...
- 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 ...
- 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 ...
- 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;...
- 3
-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), ...
- 95
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', '...
- 101
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 ...
- 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,...
- 101
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 ...
- 123