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
1 answer
967 views

Resolve parametrized schema in PostgreSQL

I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function. Why am I looking this way ? This functions will refer %ROWTYPE from multiple schema like Var1 s1....
-1 votes
2 answers
36 views

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the ...
0 votes
1 answer
359 views

Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures?

If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right? You can call a function inside a procedure create ...
0 votes
1 answer
303 views

Stored procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
8 votes
2 answers
38k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
0 votes
2 answers
57 views

Function performance is worse than raw query in PostgreSQL

I am trying to squeeze out the all the performance from my PostgreSQL database, also I want to abstract my query definitions from my application layer. To do that, I am using table-valued function. ...
0 votes
1 answer
117 views

How can functions that make heavy use of table variables be optimised?

Suppose that you have a multi-statement table-valued function that makes heavy use of table variables. What methods exist for optimising them? I only know of the following and they all disappoint me. ...
1 vote
1 answer
19 views

SQLite: How to include an Sqlean define statement in a `.sql` file?

I need to generate an SQL file containing UDF defined with the extension 'define' of Sqlean. Here is a minimum non-working dump.sql file: SELECT load_extension('path/to/sqlean/define'); SELECT define('...
0 votes
1 answer
428 views

Need a function to run different queries based on parameters sent subquery must return one column error

I am new to advanced sql programming. I am storing constants. Constants have three possible scopes: local, user and global. Two tables: s0constants_user holds set values of constants s0constants ...
0 votes
1 answer
4k views

Append JSON array to an object

I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure. id name language attr1 attr2 ...
1 vote
2 answers
50 views

Query to return latest record between 2 tables

I'm trying to pull the most current date (cusPurDt) and corresponding amount (subAmt) by customer (cusID/subID). Here is some sample data: create table customer ( cusID char(5), cusPurDt ...
0 votes
1 answer
816 views

Problem calling Postgres function

I wrote this function: create or replace function field_summaries(gap interval , _tbl anyelement, _col text) returns table(hour text, average numeric, minimum numeric, ...
0 votes
1 answer
381 views

Realtime Update MySQL on every Update occurred on PostgreSQL

I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
4 votes
2 answers
3k views

Are views logically redundant?

Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any ...
0 votes
0 answers
56 views

How to convert a function into a Stored Procedure in Microsoft SQL?

I have this function that takes a decimal parameter and returns a varchar CREATE FUNCTION dbo.NumberToWords(@Number DECIMAL(18, 2)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Words NVARCHAR(MAX) SELECT ...
0 votes
0 answers
34 views

In a scalar function on SQL Server, is the only form of flow control via If...Else?

In a scalar valued user-defined function on SQL Server 2019, is the only form of flow control the If...Else block? Ideally I would rather use CASE...END, but everything I've found indicates this is ...
0 votes
1 answer
881 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 ...
0 votes
1 answer
58 views

Convert numbers to words | Spell out DECIMAL USD 1259.75 as 'One thousand two hundred fifty-nine dollars and seventy-five cents'

ALTER FUNCTION [dbo].[NumberToWords](@Number DECIMAL(18, 2)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Words NVARCHAR(MAX) SELECT @Words = COALESCE(@Words + ' ', '') + CASE WHEN @...
2 votes
1 answer
2k views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
2 votes
1 answer
981 views

In Postgres, is an unprivileged role able to execute a function with elevated privileges?

For context, I am using: postgREST: a thin REST client on top of Postgres (which does all the heavy lifting) Azure AD: which handles identity and access of the organization Since users, roles, ...
1 vote
1 answer
418 views

Loop Block with execution doesn't persist

I need to iterate over a result set and update records from the data. I am using an anonymous block but the information does not persist. Like this... do $$ declare dados Record; begin for ...
0 votes
1 answer
468 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
89 views

EVENTDATA() function is giving only null values in sql server

I'm trying to capture logins time with a trigger using EVENTDATA() function. But It's giving Null values only for all types of events. Can anyone please help me on this? This is my Trigger CREATE ...
1 vote
1 answer
1k views

Perform update and raise notice in Postgres Function

I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened. I don'...
0 votes
1 answer
52 views

How do I save a function that I want to frequently use?

Due to the nature of how I ingest data into my SQL database it's prone to duplicates so I have the following view: SELECT IP, COUNT(*) AS Count FROM ipdb GROUP BY IP HAVING COUNT(*) > 1; Current ...
0 votes
2 answers
1k views

How use raise notice in nested and sub-function?

I'm using PgAdmin to create multiple pgsql functions. I'm beginner on this environment. I'm using 'raise notice' to help me for debugging phase. I have two functions with 'raise notice' statement. ...
1 vote
1 answer
315 views

How to trigger a function with DDL query?

I want to add a few sanity checks on my DDL statements. Unfortunately, I cannot run a validation separately due to some constraints. I am thinking to run validation inside the DB by calling a stored ...
24 votes
2 answers
15k views

Get query plan for SQL statement nested in a PL/pgSQL function

Is it possible to get the query plan for an SQL statement executed inside a user defined function (UDF)? Like with using EXPLAIN in pgAdmin or psql as client. I see the UDF abstracted away into a ...
1 vote
1 answer
135 views

track down which application / procedure / function modified the value of a column in a specific table

in our enterprise application, value in one column of a table is being modified at client side. This happen usually once in a week. i have verified in all stored procedures and other modules, value in ...
0 votes
1 answer
2k views

Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns. The syntax in the ...
0 votes
1 answer
56 views

Split lines against polygons in a PostGIS trigger

I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
1 vote
2 answers
403 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 ...
2 votes
1 answer
206 views

How to define a function accepting exactly three characters?

I tried this: create or replace function c3 (arg char(3)) returns void language plpgsql as $$ begin raise notice 'Value: %', arg; end; $$; But it accepts more than three characters: select c3('...
18 votes
1 answer
9k views

What does BEGIN ATOMIC 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 ...
0 votes
1 answer
24 views

Inversion of the string register

I'm using PostgreSQL and I have a table with serial numbers. The serial number is like this The serial number is like this zHOb55Klast, I want to make it like this ZhoB55kLAST. How can I do that?
0 votes
2 answers
41 views

MySQL Stored Function - How to get @@warning_count?

I have the following MySQL 8.0 stored function: DELIMITER $$ CREATE FUNCTION maybe_utf8_decode(str text charset utf8mb4, rowid INT) RETURNS text CHARSET utf8mb4 DETERMINISTIC BEGIN declare ...
1 vote
1 answer
64 views

Postgresql near real-time

Before asking the question I will update you on the situation I face. I have physical tables in postgresql that are continuously updated by various external flows (.NET, Java, etc...), at the moment ...
0 votes
1 answer
71 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: ...
13 votes
2 answers
42k views

Return a record with PL/pgSQL function - to speed up the query

I have a non-forking game daemon written in Perl, which uses async queries to write player stats into a PostgreSQL 9 database. But when I need to read something from database (like if a player is ...
0 votes
1 answer
41 views

Why is PostgreSQL `coalesce` not working as expected/hoped inside a function? [closed]

A simple function that looks like this: CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text) RETURNS tsvector LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT RETURN to_tsvector(...
0 votes
1 answer
7 views

How can I get a linear forecast in BigQuery?

BigQuery has no native support for linear forecasting outside of machine learning, but it does support user-defined functions, and these can accept arrays as inputs. However, (as of 2023/10 anyway), ...
0 votes
0 answers
90 views

Scalar udf abysmal performance

I have a udf. It is essentially a case statement, using ifs instead and multiple "broken" assignments of the same variable, see code below. It does not access any table. Still, on one of my ...
0 votes
1 answer
80 views

Scalar Functions Causing Plan Cache Bloat

Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below: SELECT dbo.fnSomeFunction('12345678') I have done some testing and found that ...
3 votes
1 answer
966 views

How can I find out if a SQL function can be inlined?

In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
4 votes
1 answer
4k views

Should I add set nocount for functions?

I've been reading about the benefits for all triggers and procedures having "set nocount on" implemented unless you make use of DONE_IN_PROC. I have a scalar-valued function that is called from an ...
14 votes
13 answers
10k views

How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

Given two numbers n and m, I want to generate a series of the form 1, 2, ..., (n-1), n, n, (n-1), ... 2, 1 and repeat it m times. For instance, for n = 3 and m = 4, I want a sequence of the ...
0 votes
2 answers
168 views

About version control on the programming scripts of SQL Server, e.g., stored procedures and functions

We have an instance of SQL Server (MSSQL) version 2016, and the database contains programming scripts, e.g., stored procedures and functions. For the programming scripts of MSSQL, we wonder how to do ...
1 vote
1 answer
339 views

Oracle Function Accepting Precision 2 Number

I'm trying to define an Oracle function that only accepts a NUMBER with a precision of, at most, 2 digits: create or replace FUNCTION ftest(x NUMBER(2)) RETURN NUMBER IS BEGIN RETURN 42; -- ...
13 votes
1 answer
3k views

Can declaring function volatility IMMUTABLE harm performance?

Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. ...
2 votes
1 answer
4k views

COMMIT works in one plpgsql code block, but not in another?

Every once in a while we need to backfill a bunch of data based on some complex calculations. Because we have a lot of data, we need to make sure that we are not locking a table for a long time and do ...

1
2 3 4 5
16