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
23 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 ...
-1
votes
0answers
13 views

How to order results with Postgres Full Text Search function using 2 tables with pg_trgm

I have two tables: Table author id (pk) | first_name 1 | John Table post id (pk) | author_id (fk) | content 1 | 1 | test content I've created an extra table ...
0
votes
1answer
40 views

How does the internal function to_char in PostgreSQL work?

I am working in an Oracle to PostgreSQL migration project. Today I found what I think is a PostgreSQL bug. I wonder whether I missed something in my experimentation. The following is my test code. ...
4
votes
1answer
76 views

What does BEGIN ATOMIC ... END 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
1answer
34 views

DB<>Fiddle: Custom type and function (ORA-24344)

I've made a custom version of Oracle's GetVertices() function. The solution involves creating a user-defined type and a function. Here is the code (working): CREATE TYPE vertex_type_cust AS object ( ...
0
votes
1answer
27 views

How to loop an array and sum values until condition is met?

I have a relational table where I need to loop through and sum amount values until a specified amount criteria is met. The data looks like this: Company Account Amount Coastal A384 500 Coastal C940 ...
1
vote
1answer
28 views

PLPGSQL store columns of a 2D query output into array variables

In some step of a plpgsql function I need to store a 2D query result into array variables. The following code does the job for scalars (but fails with arrays): SELECT col_a, col_b FROM my_table WHERE ...
0
votes
1answer
21 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
1answer
33 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
44 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;...
-3
votes
1answer
33 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
45 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', '...
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
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
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 ...
0
votes
0answers
30 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
635 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
198 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
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
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://...
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
22 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
40 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
313 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 ...
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 ...
1
vote
0answers
23 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) ...
2
votes
1answer
278 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 ...
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 ...
0
votes
2answers
38 views

Add a column with name as a result from a SELECT

I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table. ...
1
vote
1answer
56 views

Function to compute difference between 2 times

I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I ...
0
votes
1answer
12 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
0answers
819 views

How can I auto-create a partition in a declarative partitioned table in Postgres when a newly created row has no matching partition?

My goal here is to make a partitioned table that's simplified below Group Info A blah1 B blah2 but instead of having that as one table, I want to partition my table per group so that there's a ...
0
votes
2answers
52 views

Error when passing variable to function

I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function: CREATE OR REPLACE FUNCTION public.intercept_ddl() RETURNS ...
0
votes
1answer
116 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'...
1
vote
0answers
49 views

MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?

TL;DR; I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
0
votes
2answers
40 views

PostgreSQL - Functions-Body-Security

I'm thinking about how to hide as many attributes of relations from a user as possible & if functions can help with that. Example: The user "dummy_user" should have as little insight as ...
0
votes
1answer
28 views

Database function SELECT with optional argument [closed]

I need to perform a SELECT in postgres that filters records only when a certain argument value is not null. If the argument is null then the argument is ignored. I am experimenting with a function... ...
0
votes
1answer
128 views

Pass variable as an input argument to a function inside another function in PostgreSQL

I am new to PostgresQL and having some trouble in feeding a function with a variable from an outer function. (maybe it is not possible...) Inside a function I am trying to assign a value from calling ...
0
votes
1answer
45 views

Calculating available inventory for a perishable good

Long time reader but first post! I am designing a database that requires me to be able to calculate the inventory available for a perishable good on any given day. In my case, it is for a farm. ...
-1
votes
2answers
90 views

How to create an index based on a time-based function in postgres? [closed]

I have a function called hot_rank to rank posts, based on their score, and their newest_comment_time (basically a decaying power function): create or replace function hot_rank( score numeric, ...
5
votes
1answer
357 views

Are the results of the STRING_SPLIT() function returned in a deterministic order?

I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible). For example, take a column definition ...
-1
votes
3answers
222 views

Function: if either x or y are null, return null

I'm looking for an Oracle 18c function that evaluates to null if either of two variables is null. The Constraints: (details here) The function/expression can't contain spaces (so CASE won't work). I ...
1
vote
1answer
72 views

Index on a Computed Column(using udf)

I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for ...
0
votes
1answer
47 views

How do you call an external C function from a SQL stored procedure?

In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure: replace procedure p() sql security creator begin declare l_hash varchar(32); set l_hash = ...
0
votes
2answers
215 views

How to avoid creating and dropping temp tables in functions?

I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs. As far ...
0
votes
2answers
99 views

SELECT sql_function(...) vs SELECT * FROM sql_function(...)

I'm reading the PostgreSQL documentation for the first time and when facing SQL functions I think I have understood all the basics, but still can't see the difference between SELECT sql_function(...) ...
-1
votes
2answers
79 views

PostgreSQL custom generic functions [closed]

is there a way to create a customized generic function in PostgreSQL? I would like to create a custom FLOOR function that accepts two parameters and as a result returns a new column in the query ...
0
votes
2answers
45 views

use of age() function postgres [closed]

I am trying to calculate the time since an event took place. The event timestamp will itself be the result of a MIN aggregate function. Is there a way to do this. I have run a query to get the ...
2
votes
1answer
31 views

Check the result of a math expression in mysql

Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here. a b oper c ------------- 2 3 + 5 4 2 / 3 ...
0
votes
2answers
86 views

Should Postgres function return/coerce numeric scale/precision?

The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function ...

1
2 3 4 5
13