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
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 ...