944 questions
1
vote
2
answers
98
views
CREATE OR REPLACE FUNCTION in concurrently running transactions
How does create or replace function in PostgreSQL 14 behave with respect to transactions? If there are multiple concurrent transactions running at read committed isolation level, and trying to create ...
- 1,038
2
votes
3
answers
141
views
Correctly escape a trailing backslash in a string within XML to JSON function?
I have created a recursive SQL Server scalar-valued function that converts XML data to a JSON string. The function works well for most cases, including nested elements and handling of arrays (using a ...
3
votes
2
answers
209
views
Grouping repeated XML elements into JSON arrays
I am writing a T-SQL function to convert XML data into a JSON string. My goal is to automatically group repeated child elements with the same tag name into a JSON array, rather than creating separate ...
3
votes
1
answer
182
views
Why the same IMMUTABLE function takes more time than STABLE?
I have changed STABLE to IMMUTABLE and expecting it to work faster, but it works more slower. Did I miss something?
IMMUTABLE function:
test=> \sf+ rls_guard
CREATE OR REPLACE FUNCTION ...
- 26k
2
votes
1
answer
77
views
Entity Framework Core equivalent of getting the int result similarly to the .ExecuteScalar("SELECT dbo.ufn_xxx(...)")?
I am sharing some SQL procedures with a webforms application and also a Web API. To do that, in ASP.NET Core Web API, I can use the code shown below. The first part gets the list of records. However, ...
- 21.1k
2
votes
2
answers
94
views
Transposing the row into a column header and other as column value in Redshift SQL
Source data - table name temp:
Source
value
ID
A
C123
111
B
V123
111
A
C456
222
B
V456
222
I need to convert the column name source output as a new column header and store the column name value output ...
- 91
0
votes
4
answers
124
views
SQL Function with multiple CASE WHEN in the WHERE clause - slow performance
UPDATE
I have tried most of the offered solutions and they work only when I pass a specific 'material_id'.
When I run the function on a bigger dataset it runs forever...
I work in Azure Synapse on a ...
- 147
-2
votes
2
answers
258
views
Using SQL , how do I select the max values when there is more than one with the same max value?
I am trying to select the maximum ImportDate for each person. For this person "Bob James" he should have the last 2 rows selected with ID 267311 and 267342. I need to see every column in the ...
- 41
1
vote
1
answer
94
views
Passing array to WHERE IN on multiple columns in a function
I have a table as following:
create table test
(
id varchar(20),
text varchar(20)
);
insert into test values(1, 'a')
insert into test values(2, 'b')
insert into test values(3, 'c')
To check ...
- 153
-2
votes
2
answers
80
views
PL/SQL function to detect a one way character change between 2 strings
I want to write a PL/SQL function with 4 arguments: SEARCH_STRING, CHECK_STRING, SEARCH_CHAR and CHECK_CHAR
where the function will check the SEARCH_STRING against the CHECK_STRING and detect whether ...
- 1
-4
votes
2
answers
119
views
How to make this subquery return more than one row?
I select data from temporary table:
CREATE TEMPORARY TABLE IF NOT EXISTS "tmp"
(
"fuel_type" TEXT,
"litres" NUMERIC(8,2),
"is_rent" BOOLEAN
);
...
- 1
0
votes
1
answer
277
views
supabase function unable to access one table
I have a function in supabase that updates two tables (Messages and Games), I've now added a third (Users) and for some reason its unable to access it, I've added a debug_logs table to figgure out ...
-4
votes
2
answers
99
views
When i call this function for 700k record it is very slow
When I call this function for 700k rows it is very slow.
This function calculates the number of holidays between two dates.
ALTER FUNCTION [dbo].[HolyCount]
(
@StartTime datetime,
@...
- 17
0
votes
1
answer
145
views
Why were functions introduced when stored procedures were already available in SQL Server?
What factors led to the introduction of functions alongside stored procedures in SQL, and how do functions specifically enhance the flexibility and efficiency of database operations compared to stored ...
0
votes
1
answer
99
views
JSON_KEYS function
in the JSON_KEYS function, i dont understand what is return type:
https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-keys/
according the doc : Return Value: A JSON array
...
0
votes
0
answers
191
views
Create SQL function using Django migrations command
I am using Django Framework in Python with MySQL Database
For adding a table in the database, I create a class in models.py and then run
python manage.py makemigrations. This creates the migration ...
- 123
-2
votes
2
answers
216
views
Get particular value from string in SQL query
I am working with a stored procedure where I am getting a string with different characters I want to get a value at last index
[AV] Z_Prem_454-3000_XXXXX_800+ [InstalmentScheme 6]
above is the string ...
- 127
0
votes
1
answer
129
views
Why is the explicit table name necessary in this Postgres Function?
I'm using Postgres for the first time and am confused by an "ambiguous" error regarding my functions.
CREATE TABLE "user" (
user_id VARCHAR(40) PRIMARY KEY,
email VARCHAR(...
- 1,806
0
votes
1
answer
626
views
How do I create a PostgreSQL TRIGGER to automatically calculate columns after INSERT?
I am trying to populate some calculated columns after each new row INSERT in PostgreSQL, but I can't get it to work.
I have already read these posts (I know these are for SQLite - this question was ...
- 1,816
-2
votes
1
answer
136
views
Syntax Error #1064 when using MariaDB JSON_REPLACE function
I'm using MariaDB version 10.3.39.
I have create a table named "sys_config" with multiple columns.
A column name is "json_system". It's data type is assigned to "longtext"...
0
votes
0
answers
181
views
Error: No function matches the given name and argument types. You might need to add explicit type casts
To fetch the result, and search functionality, I took the approach of writing SQL Function and use it in the spring boot backend, using native query. To do that I have created a function as shown ...
- 53
0
votes
1
answer
47
views
I need to treat values from a string field and break it down into an array
I have a table in bigquery with a string field that I need to read, disassemble and generate a new field with several rows receiving the disassembled field
The field has values like this:
Column A
...
0
votes
1
answer
42
views
SQL error getting counts of subscribes and unsubscribes number from 2 tables
I'm getting an error on this Presto SQL code in subscribes and unsubscribes function and variable names:
SELECT
emailaddress,
firstname,
lastnamename,
role,
status,
optindate,
...
0
votes
1
answer
49
views
'column reference "id" is ambiguous' error on function call that uses RETURNING
I have this table with two autogenerated columns:
CREATE TABLE driver_orders
(
id SERIAL PRIMARY KEY,
car_id INTEGER NOT NULL,
location_from GEOGRAPHY -- generated
GENERATED ...
- 5,394
0
votes
2
answers
106
views
How to use a query returning integers in an SQL function that returns integer[]?
I created a function that returns all values that meet the condition specified and tried specifying int[] as the result type of the function.
create or replace function mults_of_3_5(id int) returns ...
0
votes
2
answers
38
views
String modification for table column values
I need a solution for string from column values like 'abc xyz-1234' to 'xyz, abc'.
What will be the Oracle SQL query for above solution.
SELECT SUBSTR (COLUMN_NAME,
INSTR (' ', ...
- 21
0
votes
0
answers
48
views
Postgres plsql return one column from table with dynamic type
I'd like to create function using pgplsql which takes table name and column name and returns this column as result.
The main problem which I faced is dynamic type of this columns (might be int/float/...
- 29
1
vote
1
answer
374
views
Processing data row by row while bulk insert using JSON in SQl server
Need to Generate Tag Number While Inserting Data using JSON in SQl Server Stored Procedure.
I have 2 Tables
Table1: Category (lastusedIndex null in initial stage)
Id
categoryname
assetPrefix
...
- 327
0
votes
2
answers
67
views
How to identify the value which was assigned before the present value that is assigned?
There are multiple historical rows present for a customer in customer table and a id has been assigned which might have changed overtime
Name
ID
Date
Abhishek
1
23-08-2023
Abhishek
1
03-08-2023
...
1
vote
1
answer
280
views
Using the SQL LAG() function
I'm trying to use the LAG function in SQL to attempt this but I'm unsure if I am using this incorrectly or not.
What I am trying to do is where you see a NULL under the LOCAL_ID column, fill that NULL ...
- 576
1
vote
2
answers
463
views
Can I use an OPTION clause inside a function in T-SQL (SQL Server)?
I want to write a function like this:
CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
WITH NumberList AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM ...
- 4,121
0
votes
2
answers
429
views
Find the least value in between the columns using Spark DataFrame
I have a dataframe like below and need to find the least value except zeros and add it in a new column as 'Least'.
Column1
Column2
Column3
100.0
120.0
150.0
200.0
0.0
0.0
0.0
20.0
100.0
I tried with ...
- 41
0
votes
0
answers
77
views
Function validate email SQL informix
I need to do a function in an older version of informix that I need to validate a mail:
so I need a function that check the email pattern [a-zA-z0-9]@[a-zA-Z0-9].[a-zA-Z0-9]
Also in the last one that ...
1
vote
1
answer
1k
views
Count of rows in pyspark dataframe over a window
I have a pyspark dataframe with below data
[
My code:
W = Window.partitionBy("A").orderBy(col("C"))
main_df = main_df.withColumn("cnt", F.count("B").over(W))
...
- 103
0
votes
1
answer
97
views
MariaDB functions cannot correctly retrieve data from INFORMATION_SCHEMA.COLUMNS
I want to create a function in SQL that takes in a field name and field type and returns a boolean value. Based on the result, I will decide whether or not to update the field type. Here is the code ...
- 30
3
votes
2
answers
69
views
Postgresql - error returned more than one row from function
My first post in Stackoverflow. I am trying to learn Postgresql (12) "on the job" and seem to be stuck on a rather simple issue. I have a simple database with 12 rows. In one column (int) ...
- 43
-1
votes
1
answer
68
views
Why is the column value not persisting after Postgres function triggers after update?
I have a table called Projects where I need to set a certain column value after an update is executed and the record matches certain conditions. The function triggers correctly and there are no errors ...
- 53
1
vote
1
answer
100
views
How can i write a postgres function that loops over array of arrays and concatenates all values into a string
I am trying to write a postgres function that takes an array of arrays (e.g. [[65.8434183140001,38.905535066],[65.8433595220001,38.905479615],[65.843286031,38.9054103],[65.843108611,38.9055251450001],[...
- 5,394
-3
votes
1
answer
111
views
PostgreSQL function has syntax error at IF. Why?
I was trying to write a function: If a row exists it returns the row with a flag 'Exists'; otherwise it inserts a row and returns it with a flag 'New'.
But I am facing a syntax error:
ERROR: syntax ...
0
votes
3
answers
389
views
SQL Query to extract the numbers before a specific word, including floating point numbers
I have data like this:
String 1: 'Random Text 3 Random 568 Text 5.5 Test Random Text 345'
String 2: 'Random Text 3 Test Text Random'
String 3: 'Random Text 777 Random Text'
The output I expect is:
...
- 360
1
vote
1
answer
83
views
when try to make database separated comma i get error invalid in the select list because it is not contained in either an aggregate?
I work on SQL Server 2012 I face error and I don't know how to solve error
Msg 8120, Level 16, State 1, Line 43
Column '#servers.ServerId' is invalid in the select list because it is not contained in ...
- 559
-1
votes
1
answer
181
views
Function is not working with varchar records
I have a function that in theory will give the country name as output when the input is the city name:
CREATE OR REPLACE FUNCTION public.country_by_city(city varchar, OUT Country varchar)
RETURNS ...
0
votes
1
answer
50
views
How to find the nearest numeric values (also in decimal places) in SQL same as LIKE function
I want to find the nearest coordinates to validate the place name as the format type is FLOAT, then I cannot use LIKE function as it is only applicable for STRING format.
situation example;
my ...
0
votes
0
answers
179
views
How does SQL STBuffer measure distance around polygons?
How does the STBuffer function measure distances around a polygon?
I am looking at the expression:
select SP_GEOMETRY.STBuffer(100)
and wondering how the 100-meter buffer is calculated around the ...
- 1
2
votes
1
answer
152
views
Generate a sequence of square numbers till 10
How to generate a sequence of SQUARE numbers till 10 in MYSQL? (1^2,2^2, etc)
I was only able to generate a numerical sequence from 1 to 10.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT ...
- 23
0
votes
1
answer
121
views
Trying to automatically insert into a table using triggers in POSTGRESQL
I am trying to make a trigger and function that inserts into the table purchases the values which have been inserted into the table customers.
Columns of table customers
1-customer_id serial PK ...
user20137453
0
votes
2
answers
296
views
how can I make result of ARRAY_AGG() function json parsable
I have a query that selects the rows from joined table as an array using ARRAY_AGG() function.
select
entity_number,
ARRAY_AGG('{"property_id":"'||property_id||'","...
- 5,394
0
votes
1
answer
235
views
POSTGRESQL How to return multiple rows from SQL function?
I'm new to databases and SQL and i've been messing around with functions on POSTGRESQL and I made a simple function to select all names from a table.
But the function, when called, returns just a ...
user20137453
0
votes
1
answer
666
views
How to use the age() function in postgresql inside a CREATE TABLE block
I am trying to make a table called Citizens in postgresql using PGadmin and inside this table there is a column called ageand I want this age to be calculated and put inside this column when I insert ...
user20137453
1
vote
1
answer
1k
views
Memoizable functions - Snowflake
When querying INFORMATION_SCHEMA or SHOW FUNCTION we could find a column IS_MEMOIZABLE.
SELECT IS_MEMOIZABLE, *
FROM INFORMATION_SCHEMA.FUNCTIONS;
None of built-in function is memoizable:
SHOW ...
- 181k