A function defined in a relational database system. Most RDBS's include numerous built-in functions and also allow for the creation of additional custom functions.
Questions tagged [sql-function]
908 questions
4
votes
4 answers
SQL NOT IN Function
Iam trying to insert a record and i want to check that it is not already present in the table.
I try
INSERT INTO emp (empno, name)
VALUES(2, 'ram')
WHERE empno NOT IN (select empno from emp);
but it shows error 'incorrect syntax near where'
Sheik Syed Mohaideen
- 191
- 3
- 6
- 15
4
votes
1 answer
Notifications in PostgreSQL with Python(psycopg2) does not work
I want to be notified when there is a new entry in a specific table "FileInfos" in PostgreSQL 12, so I wrote the following trigger:
create trigger trigger1
after insert or update on public."FileInfos"
for each row execute procedure…
ChsharpNewbie
- 1,040
- 3
- 9
- 21
4
votes
1 answer
Modify OLD to be returned by DELETE in postgresql trigger function
I have a trigger function in postgresql which will insert rows in the audit table on INSERT, UPDATE and DELETE operations. In my tables, there is a column called audit_id and I need to write the ID of inserted audit row in this field. This is my…
Sophio
- 43
- 5
4
votes
2 answers
SQL Server Rewrite Hierarchical CTE Function to a regular Select
I've been tasked with migrating a script that traverses a hierarchy and expands it. Firstly the script is running extremely slow and secondly we are moving into a far more controlled server so I need to eliminate functions. I was wondering if…
Soprono
- 215
- 3
- 14
4
votes
1 answer
Set-returning functions are not allowed in CASE in postgreSQL
I am trying to run this query was able to till some time ago. I don't know what went wrong and I started getting this error now?
Your database returned: ERROR: set-returning functions are not allowed in CASE Hint: You might be able to move the…
noobeerp
- 417
- 2
- 6
- 11
4
votes
1 answer
Set schema for objects referred to in postgres functions to same as the function itself
In my postgres functions I use the fully qualified name (e.g. schemaname.tablename) for objects within it like tables and other functions.
But in almost all the cases, I'm referring to objects that are in the same schema as the schema of the…
user779159
- 9,034
- 14
- 59
- 89
4
votes
3 answers
Average of Sum minus Minimum
I have an SQL statement that grabs the grades of different activity types (Homework, Quiz, etc), and if there's a drop lowest for that type, it drops, else, it remains. The errors are below as well as the SQL Code.
SELECT Student.firstName,…
OneSneakyMofo
- 1,305
- 2
- 19
- 33
4
votes
1 answer
PostgreSQL custom week number - first week containing Feb 1st
I'm new to SQL functions and trying to create a calendar table that displays custom week numbers, with each week starting with Saturday and ending on Friday. The first week of each year always contains Feb.1st of that year.
For example, if the day…
Sheena Yu
- 43
- 4
4
votes
1 answer
EF: SQL Function as property
Is it possible to add scalar value SQL Functions as properties in EF?
I have some functions which sum different values in an OrderLine table that take an OrderId (ie, SUM of OrderLineCost).
I would like to be able to access these via the Order data…
Beakie
- 1,948
- 3
- 20
- 46
4
votes
1 answer
MySQL function to compare values in a db table against the previous
Iam quite new to functions in SQL and I would like to create a function to compare values in a MySQL table against previous and I am not sure how to do this.
For example (iId is the input value)
DECLARE pVal INT(20);
DECLARE val INT(20);
SELECT…
aHunter
- 3,490
- 11
- 39
- 46
4
votes
0 answers
FUNCTION database.AddDays does not exist
I'm trying to use dbfunction from EF6 on database MySQL but I get the follow exception "FUNCTION database.DiffDays does not exist".
I used this code:
var aux = bd.Atos.Where(w => w.IdAtivo == 1 && w.IdUtilizador == idUtilizador &&…
fmalh3iro
- 384
- 2
- 7
4
votes
2 answers
Linq-to-Entities SqlFunctions: How to Convert from int to double? or decimal?
I am trying to sort some articles using Linq to Entities. I am using System.Data.Objects.SqlClient.SqlFunctions. This is what I would like to have:
this.Queryable = this.Queryable
.OrderByDescending(v => v.UpVote + SqlFunctions.Log10(
…
gabnaim
- 1,103
- 9
- 13
4
votes
3 answers
INSERT return type?
I realize "an INSERT command returns a command tag," but what's the return type of a command tag?
I.e., what should be the return type of a query language (SQL) function that ends with an INSERT?
For example:
CREATE FUNCTION messages_new(integer,…
ma11hew28
- 121,420
- 116
- 450
- 651
4
votes
4 answers
Use columns from select statement as parameter in function when joining
Consider the following sql query:
SELECT TaskId,FromItemId, IHV_FROM.NodePath as FromPath
FROM VIEW_TASKS with (nolock)
left Join fn_ITEM_HIERARCHY(FromItemId) IHV_FROM ON FromItemId = IHV_FROM.ItemId
WHERE ...
fn_ITEM_HIERARCHY is a function…
Jan Petter Jetmundsen
- 546
- 6
- 16
4
votes
3 answers
pdo defining a function an then using it in a select statement
My query is somewhat like this :
CREATE FUNCTION func ... ;
SELECT * FROM ....;
I'm using php's PDO which doesn't allow more than 1 statements in a $pdo->query() , so instead I decided that I'd split the query into two, so I execute the CREATE…
Peeyush Kushwaha
- 3,453
- 8
- 35
- 69