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.
498
questions
0
votes
1answer
7 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
0answers
28 views
Convert Excel function to PostgreSQL [closed]
I am looking to convert this Excel function to PostgreSQL.
=INDEX(position!A:A,MATCH(species!C2,position!D:D,-1))
The function uses two tables.
It looks up the for same values between the D column ...
0
votes
1answer
24 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
27 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
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
33 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
46 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 ...
22
votes
1answer
447 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
67 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 ...
1
vote
3answers
896 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
65 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 ...
0
votes
1answer
20 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
53 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
222 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
213 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 ...
0
votes
1answer
20 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
109 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
47 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
30 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
25 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
472 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
66 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
63 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
25 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
61 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
34 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
21 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
297 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
162 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
138 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
42 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
0answers
55 views
Postgres - Ignore result of function call in select statement
I would like a way to run a function on all values returned from a query where the return value of the function is unimportant (even void in this case). A select statement may be the wrong way to do ...
2
votes
1answer
74 views
How to find function definition
How can I find the definitions of below system functions in SQL Server:
has_access
permission_name
I tried with DAC connection but getting below error:
Msg 15009, Level 16, State 1, Procedure ...
0
votes
1answer
41 views
PostgreSQL functions inside where clause
I have table like this:
table a (
id
--
p1 text,
p2 text,
p3 text
)
When I write some function, which use this table, I need to write code like this
create or replace function ...
...
1
vote
1answer
500 views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
4
votes
1answer
103 views
SQL Function, get value to send to CLR
I have a CLR to use the powershell to have access to the RichTextBox Object from .net.
On the SQL Function I have created like this:
CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @...
2
votes
1answer
38 views
Postgres triggers for group_id
I have a table that looks like this:
CREATE TABLE transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);
I'd like to update the group ID automatically with ...
2
votes
3answers
218 views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
0
votes
2answers
93 views
Postgres list constraints that use a stored procedure
I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.
something like:
CREATE TABLE payment (
amount bigint,
CONSTRAINT amount_gt_0 CHECK (...
0
votes
2answers
57 views
How to efficiently handle querying for different combinations of optional parameters
Consider the following Tables:
Table: Parts
part_id INT PK
part_number VARCHAR
Table: Jobs
job_id INT PK
job_number VARCHAR
Table: Job_Parts
part_id INT FK PK
job_id INT FK ...
0
votes
1answer
96 views
SQLServer Row level security - Enforce from within database
Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is unique for each tenant) and the column exists in all tables.
CREATE ...
1
vote
0answers
10 views
Querying a synonym (for a table using linked server) gives no data in return
I have a function which run in an application at a server. This server have a ODBC connection (user DSN) to the SQL server which is set to use windows authentication.
The function is supposed to ...
0
votes
1answer
105 views
Custom return type of a function in Oracle PL SQl
I have a query and I want to wrap it into a function. Currently query returns in_count, in_sum, out_count, out_sum. I only know how to return a "primitive type". So currently I have four different ...
1
vote
1answer
931 views
How to use variables in procedure (not function)? [closed]
As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:
declare a variable
select a value into a variable
insert the variable into ...
1
vote
1answer
33 views
Return table with separate columns from function
I have this function, it's simple enough:
CREATE OR REPLACE FUNCTION test_function()
RETURNS TABLE(
id INTEGER,
organization_id INTEGER,
email_address CHARACTER VARYING
)
AS $...
5
votes
1answer
198 views
PostgreSQL UDF (User Defined Functions) overhead
Disclaimer
The task may seem esoteric, but nevertheless I want to create a POC of some sorts.
The goal
My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
0
votes
1answer
63 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 ...
1
vote
1answer
1k views
What is included in ALL permissions for functions in PostgreSQL?
I am trying to find references to what the difference between EXECUTE and ALL is in:
GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }...
but all I can find is what ...
