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
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 (...
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 ...
-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?
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 ...
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 ...
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 ...
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 ...
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 ...
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'...

1
2 3 4 5
15