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.
516
questions
0
votes
0answers
12 views
Inserting result of UDF into Hive table [on hold]
In Hive, I have a UDF which is created as follows -
create temporary function exampleUDF as 'com.hive.udf.exampleUDF'
The UDF returns a string and takes no parameter.
When I run a select statement on ...
0
votes
0answers
11 views
Custom methods in MySQL / MariaDB ORDBMS
According to the book I am reading ORDBMSs come with an option to write a program (method) in Java (or other programming language), compile it into Java byte code and register via some SQL command.
...
1
vote
0answers
47 views
Optimising filter with dynamic computed column
I need to implement user risk scoring in model
The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, ...
0
votes
1answer
19 views
Is there a postgres function documentation pattern that is similar in concept to javadoc?
I am mostly a Java developer. Today I am writing some postgres functions. Out of habit I keep trying to document these functions using a Javadoc-like comment style describing the function parameters ...
3
votes
2answers
41 views
How can I determine which permissions needs to be assigned to the newly created login?
We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use ...
0
votes
1answer
24 views
Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL
I have this statement which generate a random string:
SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM ...
0
votes
1answer
33 views
Postgres Pass Subquery results to function that returns TABLE
I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I ...
1
vote
1answer
22 views
ALTER TABLE ADD CONSTRAINT failing when executed from function
So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.
The drop constraints function works ...
0
votes
3answers
27 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 ...
0
votes
2answers
44 views
How to get the current row in postgres function?
I have a table where I have a column called event varchar(255) and user. An event column is a set of strings which tells me in what state was the user when the event was received.
I want to count how ...
0
votes
0answers
19 views
Converting User-Defined Datatypes to JSON in Oracle
I'm trying to use User-Defined Datatypes in Oracle to handle flexible fields in an application:
CREATE OR REPLACE TYPE udf_typ AS OBJECT (
name VARCHAR2(4000),
value VARCHAR2(...
1
vote
1answer
28 views
Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text
I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this:
Some code...
<div> </div>
<div>[flv:http://submarino-libre....
0
votes
0answers
28 views
User-defined harmonic mean function performs worse than query in PostgreSQL 9.6
We created a harmonic mean function based on this blog post:
CREATE OR REPLACE FUNCTION public.harmean_accum(numeric[], numeric)
RETURNS numeric[]
LANGUAGE sql STABLE COST 1
AS $h$
SELECT array[$1[1]+...
0
votes
1answer
18 views
How to check and change function volatility in Postgres?
I have a function that I think is defined as volatile, but I now know I want it stable.
How can I check the function volatility (volatile, stable, immutable) of a certain function?
When a function ...
0
votes
1answer
52 views
Part of parallel execution plan has serial portion
I have a big linq to sql query which runs in parallel mode.
It is returning results in 8 seconds and it is pretty good.
I noticed in the execution plan that the extreme right part(from where execution ...
0
votes
1answer
25 views
Multiple THEN outputs on a PostgresQL query
Helllo there,
I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis.
...
0
votes
0answers
28 views
How to get custom function result in query in MongoDB
I have experience in MySQL and new to MongoDB. What I need to achieve is below.
MongoDb collection is like:
What I want is a query similar to MySQL as:
Select title,binary,hummingDistance(this....
5
votes
1answer
281 views
Why do user defined scalar functions require the schema?
When creating a user defined table function, or a user defined procedure, you can call the function or procedure as named.
However, a user defined scalar function requires that you call it with the ...
5
votes
1answer
252 views
Can’t run a function against EXEC
My SQL Server book says you can't run a function inside EXEC and shows an example:
DECLARE @NumberOfLetters int = 15;
EXEC(‘SELECT LEFT(Name,’ + CAST(@NumberOfLetters AS varchar)
+ ‘) AS ShortName ...
2
votes
1answer
85 views
Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL
Hi everyone hope you're doing well
I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors .
this is my function
create or replace function ...
1
vote
1answer
39 views
In PLPGSQL assign a variable based on result of SQL function
I have a PLPGSQL function which receives an address in two parts:
num = '123'
location = 'main street, anytown, KS'
To utilize the various portions of location I found there is a SQL function ...
0
votes
3answers
51 views
Usage of aggregate function with when self-joining a table
I want to find people from a database who has a weight bigger then the average weight:
SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)
But I have an error:
Result: misuse of ...
0
votes
1answer
48 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 ...
24
votes
1answer
488 views
Self referencing scalar function nesting level exceeded when adding a select
Purpose
When trying to create a test example of a self referencing function, one version fails while another one succeeds.
The only difference being an added SELECT to the function body resulting in ...
3
votes
1answer
68 views
How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration?
I have a function with 5 parameters,
arguments p1, and p2 are mandatory
arguments p3, p4, and p5 are optional and have a DEFAULT set
The function looks like this,
CREATE FUNCTION test_func(
p1 ...
2
votes
3answers
3k views
PostgreSQL 11 error: column p.proisagg does not exist
Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error:
ERROR: column p.proisagg does not exist
LINE 18: ...
0
votes
1answer
67 views
Cannot get a user function to co-operate with a subquery in mariadb
The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.
Notes:
Problem ...
2
votes
2answers
78 views
Generic type functions in T-SQL
Consider the following function
CREATE FUNCTION dbo.SafeDivide
(
@a numeric,
@b numeric
)
RETURNS numeric
AS
BEGIN
RETURN CASE
WHEN @b = 0
THEN 0
...
0
votes
1answer
28 views
mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF
I do not understand why MySQL is not using the index for the below query.
I have a table named users and its create table is
this table contains data.
Now I am running SQL select statements and it'...
1
vote
1answer
59 views
Should I write a scalar function or a setof returning function for an INSERT/UPDATE/DELETE which can return at most one row?
I want to embed certain INSERT, UPDATE or DELETE statements into a function, to enhance re-usability. These functions have in common that the are manipulating a single row (if it exists).
An example ...
10
votes
1answer
225 views
function to receive a character input and return date format (with incorrect input)
I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect ...
9
votes
1answer
225 views
Apparently, my CLR assembly function is causing deadlocks?
Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL ...
10
votes
3answers
324 views
Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode
One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
0
votes
1answer
23 views
Postgresql table function: get where condition
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.
Here, there is a very simple example:
create or replace function public....
0
votes
1answer
169 views
Trigger for multiplying two columns from different tables with a functions in mysql
I have been endelessly trying to get a trigger to work, with many different variations and all ending in errors.
I have finally just decided to ask for help, since im still a beginner at this.
So ...
3
votes
1answer
48 views
PostgreSQL 11: Replace function body by users from the same group
In the documentation of CREATE FUNCTION is stated that ''You must own the function to replace it (this includes being a member of the owning role).''
Two questions:
I don't understand what exactly ...
0
votes
2answers
37 views
Default values in function, arguments dependency, PostgreSQL
I would like to write a function with two arguments, which would have dependency. Something like this:
CREATE OR REPLACE FUNCTION years_compare(
IN year1 integer DEFAULT date_part('year'::...
0
votes
0answers
33 views
Is it possible to execute a postgresql function when any view is refreshed?
I want to alert my application that are utilizing views when the view is refreshed. Is this possible using PostgreSQL built-in functionality? If not are there any other approaches to tackle this?
1
vote
1answer
754 views
PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call
This is my remote function:
CREATE OR REPLACE FUNCTION public._test1()
RETURNS record
LANGUAGE plpgsql
AS $function$
DECLARE
rec record;
BEGIN
select 1,2 into rec;
return rec;
END $function$;
...
1
vote
0answers
110 views
How do I parse Address into Street Number, Street Name, Type, Unit Number?
How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this?
2280 MEYERS AVE
ALDERGROVE AVE & S ...
0
votes
1answer
126 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
26 views
What's the most appropriate type of index to use for this function-based query?
I'm using Postgres 9.5. I'm going to be running a lot of this kind of query
SELECT *
FROM mydb_article
where regexp_replace(url, '\?.*$', '') = :url_wo_query_string
so I figured I would need to ...
0
votes
1answer
63 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
0answers
40 views
Returning a set datatype from MariaDB function
Given below is the syntax for the MariaDB's CREATE FUNCTION:
CREATE [OR REPLACE]
[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([...
0
votes
0answers
22 views
PostgreSQL Function not returning message in Python 2.7 Script using EXCEPT NOTICE
I was not able to make the PostgreSQL function return the raised message in the Python script it was being called from.
The scenario I wanted to make was:-
1) If the first table was created, a ...
0
votes
0answers
385 views
PostgreSQL Function: Returning Boolean Values depending on whether a table exists
This is my rookie attempt to create a function where it would return True or False depending on whether a table exists in the db. Unfortunately it outputs an empty table, and no raised messages;
...
0
votes
1answer
192 views
Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared
I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors.
The query below informs you that ...
1
vote
0answers
18 views
Filtered Merge Statement [duplicate]
This is, probably, a really simple answer, but I am not coming up with it... Any help would be greatly appreciated:
I created a web form, to a stored procedure, that submits userID, deptID, and a ...
-1
votes
1answer
156 views
How to Set the Transaction Isolation level in a Table Value Multi Function
How do I set the transaction isolation level within in a table value multi function? I know it can be done in a stored procedure.
create function dbo.Test()
RETURNS @TestTable Table (test varchar(...
0
votes
1answer
52 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 ...
