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
1answer
1k 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 ...
7
votes
1answer
4k views

PL/pgSQL issues when function used twice (caching problem?)

I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem. I have this function: CREATE FUNCTION sp_connect(mail character varying, passwd character ...
0
votes
1answer
95 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
1answer
171 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
1answer
14 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 ...
0
votes
3answers
252 views

How to prevent trigger from overwriting values that were explicitly set?

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?): create temp table ...
10
votes
1answer
7k views

How to use an array as argument to a VARIADIC function in PostgreSQL?

I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module. I would like this to be a simple wrapper around the built-in function, the only difference being ...
11
votes
10answers
7k 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
1answer
28 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
1answer
102 views

relation "table" does not exist

i created the following schema , inside that schema i created a table and inserted a some values. After that i created a function that get a value in the table based on an ID. Here is all the ...
8
votes
4answers
6k views

MySQL possibility to create global routines (stored procedures and/or functions)

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database. When I tried to create a routine from console (without prior ...
0
votes
1answer
42 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;...
0
votes
2answers
279 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. ...
2
votes
3answers
319 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
14
votes
5answers
17k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however ...
0
votes
1answer
95 views

MADlib apply `cosine_similarity` on term frequency

I am working on a small topic modelling project and I have stumbled upon the idea of developing it in MADlib and PostgreSQL. MADlib provides this term frequency function that converts my documents ...
-3
votes
1answer
30 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), ...
0
votes
1answer
110 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
1answer
43 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', '...
5
votes
2answers
1k views

Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?

Based on Query Performance and multi-statement table valued functions and other articles, SQL Server assumes that a multi-line table valued function returns one row. This causes the selection of a ...
3
votes
1answer
114 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
0
votes
2answers
44 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 ...
0
votes
1answer
1k 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
1answer
11 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,...
0
votes
1answer
149 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 ...
0
votes
1answer
169 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 ...
1
vote
1answer
238 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; -- ...
0
votes
0answers
24 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 ...
25
votes
4answers
33k views

In PostgreSQL, is there a type-safe first() aggregate function?

Full question re-write I'm looking for a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS ...
0
votes
0answers
25 views

What is the alternative of PL/Java for PostgreSQL 11 and 12?

Understand from: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/11/EDB_Postgres_Advanced_Server_Guide.1.80.html that PL/Java is deprecated in Advanced ...
2
votes
2answers
389 views

How to create a postgres function that returns a value

I am trying to move some of my web app logic into postgres functions. But I am hitting some errors while creating a very basic insert function. This is the function that I am trying to create; CREATE ...
1
vote
2answers
174 views

How can a cheap function in a SELECT make the whole query slow?

I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts). I can't figure out why Query2 below is so much slower than Query1. They ...
0
votes
1answer
913 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 ...
0
votes
3answers
30 views

How to return a number surrounded with parenthesis as a negative number

I have a table CREATE TABLE tab1 (col1 integer, col2 character varying(20)); Col2 contains numeric value but the negative values corresponds with parenthesis not and minus (-). This is the sole ...
0
votes
1answer
223 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 ...
0
votes
0answers
32 views

Is there any way I can optimize this merge ranges function?

I have this this function which takes in an array of tsranges, and convert them into one array with tsranges, where each range contains all overlapping ranges from the input. Version: 12 https://...
4
votes
1answer
10k views

When to use stored procedure / user-defined function?

I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database. Is there any general advice to ...
0
votes
0answers
2k views

Query performance become slower after upgrade to MySQL 8.0.20

I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20. When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable. But ...
0
votes
1answer
33 views

Stream every row coming from a Postgres function

This is the how the function definition looks: CREATE OR REPLACE FUNCTION my_function(foo NUMERIC) RETURNS SETOF my_type AS $$ DECLARE rec record; BEGIN FOR rec IN SELECT * FROM my_table WHERE ...
1
vote
1answer
21 views

Stored procedure support in MySQL Workbench?

I've created a lot of stored procedures when I was using HeidiSQL on Windows. I am now on an iMAC (x64) and it doesn't look like installing HeidiSQL is an option. I've got MySQL Workbench version 8.0....
1
vote
1answer
36 views

Using a commit inside plpgsql block works in one case not 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 ...
0
votes
2answers
226 views

How to make a function in postgresql with two parameters to return count from column table?

I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year. The table is something like this: CREATE TABLE orders ( id INT NOT ...
23
votes
2answers
8k views

Postgres query plan of a function invocation written in plpgsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed inside a user defined function (UDF) using EXPLAIN. So how do I get hold of the query plan for ...
1
vote
1answer
30 views

Junction table Trigger after update in another table

I'm just trying to create a trigger that performs an update in a junction table. In principle, I have three tables with Many-to-Many relationship. Table a: a_id, a_is_active Table b: b_id, b_is_active ...
2
votes
1answer
222 views

How to select sub-object with given keys from JSONB?

How to extract / get / select a "sub object" from a json(b) object in Postgres? There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
1
vote
0answers
22 views

PostgreSQL - Dynamic filter expression

Given a simple function (SQL for the sake of example, can be plpgsql) CREATE OR REPLACE FUNCTION dynamic_filter( f INT[] DEFAULT NULL ) RETURN INT[] LANGUAGE SQL AS $$ SELECT ARRAY_AGG(val) ...
1
vote
1answer
171 views

Error trying a manual's example in DB2 11.1.4.4

When trying this example from the DB2 11.1 manual: VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER)); I get this error: SQL0171N The statement was not processed because the ...
6
votes
2answers
1k views

How can I eliminate this scalar function or make it faster?

I'm working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql ...
9
votes
4answers
13k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
3
votes
2answers
13k views

Is there an easy way in PostgreSQL to escape newlines, carriage returns, and tabs?

Let's say I want to escape these to see a similar string SELECT * FROM ( VALUES (E'\r\n\t FOO BAR \t\n\r') ) AS t(str); How do I get the c-style escapes, \r\n\t FOO BAR \t\n\r back out? SELECT * ...

1
2 3 4 5
13