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.
724
questions
2
votes
1
answer
103
views
ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function
I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.
There is any ...
- 247
1
vote
3
answers
49
views
Alter a scalar UDF that is used in a check constraint in SQL Server 2012
I have a check constraint on a column that uses a scalar UDF. I have a requirement where I need to make changes to the UDF. Based on what I know, I need to drop the constraint, make changes to the UDF,...
- 149
1
vote
2
answers
61
views
PL/pgSQL select statement inside trigger returns null using where NEW
I'm creating a trigger that triggers ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS ...
- 36
1
vote
0
answers
35
views
PostgreSQL - updating a counter outside a loop function
PostgreSQL 14
Here is a function :
CREATE OR REPLACE FUNCTION test(datedebut integer)
returns text
LANGUAGE plpgsql AS
$$
DECLARE
tab integer[];
periodes text;
i integer;
BEGIN
tab := Array[[0,...
- 197
0
votes
1
answer
18
views
Composite data type returned from one postgresql function seems to arrive differently in the caller
I have the following composite data type in a postgres db:
CREATE TYPE IncreaseCredentialsType AS
(
increase_account_id text,
api_key text,
url text
);
I'm returning a value of this type at the ...
- 101
0
votes
1
answer
49
views
MS SQL Server: Dynamical create procedure / function / view / trigger in other database (sp_executesql)
Problem:
you want / need to use dynamic SQL to create procedure / function /
view / trigger in other databases than the current
you can't specify the database name in the statement, CREATE VIEW ...
- 845
0
votes
1
answer
19
views
Is it slower to pass a composite type representing a table to a function then the individual arguments?
Let's say you want to create a function
CREATE FUNCTION f(a,b,c,x,y,z) FROM t;
Is it any slower to restructure the function such that this works,
CREATE FUNCTION f(t) FROM t;
- 59.6k
0
votes
1
answer
36
views
Time series analysis and custom aggregate function for Postgres
I have a table with time series. It contains snapshot and increment value
snapshot means concrete value
increment means take previous snapshot and add increment value to get snapshot value
sensor_id ...
- 123
0
votes
0
answers
27
views
Is calling a stable function performance comparable to reading values from a table?
Let's say we have a function _test_has_group_feature, which is meant to check if a group (identified by a name) has a particular feature (identified by name).
_test_has_group_feature(group_name, ...
- 1,076
3
votes
1
answer
167
views
Error Sql (1064) creating a function in MariaDB
I am getting an error in MariaDB when trying to create a function. It gives me the following error and I leave the script below.
The script:
CREATE PROCEDURE `total_hh`(fechainicio datetime, ...
0
votes
0
answers
90
views
PostgreSQL lower case or remove double quotes on every table name before select query
We are migrating from OracleDB to PostgreSQL and all the tables name in database are lower case while the script from application is mixed with double quotes.
For example the code in application like ...
2
votes
1
answer
71
views
How can I force a system function to use an index in SQL Server?
Good morning team,
I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what ...
- 41
0
votes
1
answer
85
views
Is it possible to grant EXECUTE permissions on a custom function without compromising read-only access?
We have an ancient database instance - Microsoft SQL Server 2008 R2 SP1 - that is running an (also obsolete) business-critical application. I know this is horrible, but for now it's not my call.
Long ...
- 103
0
votes
1
answer
43
views
how to create a calculated column
I want to create a table, this table's name is "Prod", in his table, I have the:
ID, name, Buying_price and selling_price
when the user inserts the data, I want him to enter "name, and ...
- 41
0
votes
1
answer
48
views
postgresql script to list all functions including create date and modify date
I have been deploying many functions into different databases in different servers in PostgreSQL.
I would like to have a script that would show me a list of functions in the current database including ...
- 15.4k
1
vote
1
answer
108
views
Create postgres trigger that will populate additional table column on insert
Problem
(Note: I have added potential solution to the bottom of the post)
I don't know what's wrong with the approach that I currently have, but when I try to run it dbeaver just instantly quits.
I ...
- 226
0
votes
1
answer
62
views
SQL : How to automatically run different SELECT query on a daily basis base on the current system date
I have 31 tables one for each day of the month where the table names are like subscription_x where x is the day of the month
On a daily it should run a select * from subscription_x to get data from ...
3
votes
1
answer
152
views
Invalid Object Name after Creating a Table using a while loop
I created this loop to create and populate a temporary table for each int representing a day. The tables are created and properly executed but i get Invalid Object Name everytime i try to print a ...
- 31
0
votes
1
answer
46
views
Recursive trigger into function
I have problems with recursive trigger: the table that fires the trigger and give a specific error for that; the trigger inserts a record, that fires the trigger, that fires on the table, that fires ...
0
votes
1
answer
179
views
Infinite running loop while executing procedure
I have this table with 3 records. 3 records are create scripts. I need to execute the create scripts and if there is a 208 error occurred then I need to keep the record and set status as 1. Then ...
- 95
8
votes
1
answer
592
views
Why won't this UDF work in this check constraint?
I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel ...
- 81
2
votes
2
answers
470
views
Invalid object name error while creating function
I have this function get_department. Inside the function there is a function call for get_po_amount.
The get_po_amount function doesn't exist anymore, so when I try to create the Main function ...
- 95
3
votes
1
answer
111
views
XML vs UDF performance in SQL Server
I have a situation where I have to decide between receiving XML as an input parameter to the stored procedure or a list of comma separated values and parse them using a multi-table valued function.
...
- 319
0
votes
1
answer
52
views
SQL query to script all objects from a database in order
i have a script to get all objects (functions ,views ,triggers and procedures from a database . I need to create this in another database . The problem is some procedures may have another procedures ...
- 95
0
votes
0
answers
31
views
PostgreSQL: Python UDF not shown in query plan obtained by EXPLAIN ANALYZE
I have a question regarding the execution of a Python UDF. Let's suppose I have a UDF named testUDF(...) and I apply it to table "testtable". When I run the query SELECT testUDF(...) from ...
- 3
0
votes
1
answer
79
views
Alter a function in Postgres 12 to allow for dates to be default or populated [duplicate]
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 389
0
votes
0
answers
155
views
TSQL (SQL Server) Table-valued function whose return-form depends on parameters
I have a table who has an arbitrary number of bit columns which can change over time.
Key
Bit1
Bit2
Bit3
'something1'
1
0
0
'something2'
1
1
0
'something3'
0
0
1
What I am trying to do is set up ...
- 101
0
votes
2
answers
238
views
Default values for function parameters
Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.
CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, ...
- 389
0
votes
1
answer
505
views
Return type mismatch in function declared
I am working in supabase, where I want to make a function with the help of SQL queries to obtain the last record of a user.
As it is a query that would not look entirely good using the traditional ...
- 101
0
votes
2
answers
156
views
What if we do not use MAX function
I was supposed to use a script to get the MAX value from a table. It should have looked like this
BEGIN
DECLARE @MaxID AS INT
SELECT @MaxID = MAX([Id]) FROM dbo.suggestion
Insert into suggestion
...
- 121
0
votes
0
answers
28
views
How to get executing result of a 'DROP' in a custom function in Postgresql?
I have thousands of tables with same structure in a Postgresql database, and each of them has a index with same suffix e.g. '_brok_name'.
I need to drop all those indexes and re-create.
To prevent ...
- 205
0
votes
0
answers
14
views
To_number() function with rpad() function , how it works?
I saw this piece of code and I have no idea how it works . I was wondering if you could help me figure it out :
column Col_one data type is number and you can see the code below:
select to_number([...
- 1,390
2
votes
1
answer
65
views
Need to understand why the same function call on two different SQL Servers result in vastly different plans
I have two SQL Server 2019 instances, one at a customer site and one in our lab. A function call which determines if an executing trigger is the LAST (or not the LAST) trigger is producing vastly ...
- 365
2
votes
5
answers
307
views
SQL Loop Use Case - Not Sure How or Why These Are Useful
What is a good use case for writing a loop in a SQL function? I can see a loop as useful for generating random data, but apart from that, I can only think of a few other reasons.
For example, you may ...
- 119
4
votes
1
answer
339
views
Scalar function alters estimate for preceding operator
Please could someone help me understand why the presence of a scalar function is changing the estimate of a preceding index scan?
I'm using a copy of the StackOverflow2013 database.
SQL Server 2019 (...
- 391
0
votes
1
answer
64
views
How to return rows from multiple tables with a Postrgresql function
This is the selection I want to make:
select figurita.codigo,figurita.imagen, jugadores.nombre, jugadores.apellidos, jugadores.numero,jugadores.peso,jugadores.altura,jugadores.fechanac,
posiciones....
0
votes
1
answer
310
views
How do you pass the array of composite type to the argument of an SQL function?
Database example: https://dbfiddle.uk/sERgZPiB
Tables and types
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL,
password text NOT NULL,
email ...
- 176
-1
votes
1
answer
51
views
What are the use cases for Out and In-Out Parameters in Postgres?
I understand what Out and In-Out parameters are, but I can't seem to understand a good use case for them. Is it to make functions more descriptive? Or is there a practical benefit/need for using them?
- 119
2
votes
1
answer
91
views
How do you run "SELECT" on the composite type argument of an SQL function?
Example: https://dbfiddle.uk/bCSwVpd9
Database:
CREATE TABLE entities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TYPE entity_id AS (
id bigint
);
Function:
CREATE FUNCTION ...
- 176
2
votes
2
answers
201
views
0 and 1 FROM DUAL Matrix
I need to make a matrix with 0 and 1 combination :
SELECT 1 AS F1, 0 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 AS F1, 1 AS F2 ,0 AS F3,0 AS F4, 0 AS F5 FROM DUAL
UNION ALL
SELECT 0 ...
- 29
0
votes
2
answers
934
views
Postgres 11 passing a list of uuids to function
Im trying to create a function that accepts a list of uuids as a parameter.
My code:
create or replace function get_child_groups(input_parents_id uuid[])
returns table(id uuid, name text, parent_id ...
- 127
3
votes
1
answer
101
views
PgSql: how could I achive a group by that checks any match on any of two columns?
I am trying to implement a NMS filter for labels in a Database.
NMS is a filter that removes redundant squares. On the image provided we can see there is only one tree, but there are three labels ...
- 51
0
votes
1
answer
48
views
Help Troubleshooting Full-text Search Function Performance
Query finds posts with a 10 mile-radius of postal code. Response time is much too high
Number of rows in the PG Materialized Table: 520,000
Any help would be appreciated.
Query:
EXPLAIN ANALYZE SELECT ...
- 1
0
votes
0
answers
34
views
Apex page DML Mail Alert Trigger
I have a Trigger and Function which sends mail with "updated" column(s),
when i am updating in my back-end (sql developer), i receive a mail with that particular column.
But when i do UPDATE ...
1
vote
0
answers
28
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 ...
- 197
1
vote
1
answer
95
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 ...
- 111
4
votes
1
answer
583
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 ...
- 185
0
votes
2
answers
56
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 ...
- 125
11
votes
2
answers
803
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.
...
- 36.7k
0
votes
1
answer
25
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'...
- 143