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.

learn more… | top users | synonyms (2)

6
votes
3answers
92 views

Azure Data warehouse - User defined function issues

Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an ...
0
votes
0answers
4 views

Save relation into postgres function variable to use in CORR() call?

I have two tables, queries and signalsand I want to create a Postgres function to calculate the CORR() for all signals, given a query. I'm trying to simply call CORR() with two variables in the ...
3
votes
1answer
42 views

Converting SQL SERVER Procedure to POSTGRESQL

I have procedure in SQL Server to print all players of given country. I try to convert it into plpgsql, but I get errors. SQL Server Procedure CREATE PROCEDURE kraj_pilkarze @kraj varchar(30) AS ...
1
vote
1answer
34 views

mysql query parent child relation get childs

I have a user table in mysql. per user has got a parentuserID in the same table. i want to crate a function that give me the all child users when i give the UserID. my table structure is below. DROP ...
1
vote
1answer
32 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
0
votes
3answers
73 views

Syntax on Changing return String by Concatenation?

I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,..) and Vendors(VendorName, ...
1
vote
0answers
33 views

Function for matching email address returns full table of emails

I am using Postgres 9.4.6. I have created a Function for finding email address that match an email variable that is passed. However, when I search for 'user@email.com' I get a full table of email ...
3
votes
1answer
17 views

Run Built-In Function on linked server

We have two SQL 2014 servers, and I have created a Linked Server on one of them to the other. As part of a task I'm trying to run I need to convert a SQL UserID to a username on both the local and the ...
19
votes
9answers
4k views

Test if a string is a palindrome using T-SQL

I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an ...
0
votes
2answers
36 views

Function Variable Number of Arguments plsql

I am looking to do a function with non fixed number of arguments like decode function. The syntax for the DECODE function in Oracle/PLSQL is: DECODE( expression , search , result [, search , ...
2
votes
3answers
98 views

Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc. There are a ...
1
vote
3answers
51 views

How to replace column if not null in select statement?

I have a Contact (ID, FirstName, LastName, MiddleName). For example: | ID | FirstName | LastName | MiddleName | |====|===========|==========|============| | 1 | Mark |Jordan | E | ...
0
votes
1answer
99 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
0
votes
0answers
33 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
1
vote
1answer
32 views

Is it possible to create a function for transactions that use more than one database?

As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have ...
-1
votes
1answer
62 views

Need help with this policy function: Shows compilation errors [closed]

Create or Replace FUNCTION Hello_Fun ( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS V_Hello Varchar2(400); BEGIN If User = 'Admin' then V_Hello := ''; Else V_Hello := 'cus_id ...
1
vote
0answers
42 views

pg_restore slow after adding a check function

I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records. The way I did this was to remove the foreign key on some tables in ...
1
vote
2answers
32 views

Loop over string literals as input to function PostgreSQL 9.4

I have a function called update_total which looks like this: CREATE OR REPLACE FUNCTION update_total (table_name CHARACTER VARYING, year CHARACTER(4), donor_type CHARACTER VARYING, donor_code ...
1
vote
2answers
73 views

Revoking execute access on builtin functions on SQL Server

I am trying to lock down a SQL Server database server and I want to revoke execute access from all users on specific builtin functions such as LEFT, RIGHT SUBSTRING, etc. When I try to execute: ...
10
votes
3answers
147 views

Theil-Sen estimator function in T-SQL

Does anyone have a Theil-Sen regression function written in T-SQL? I found one written in Perl, but I'm not able to recode it into SQL.
1
vote
1answer
21 views

MAX and MIN for each car model

I have a problem with a query. I have a db with car and their spec, every model has a different car related to that model with its price. I want to create a query that gives me the min and max price ...
0
votes
0answers
22 views

Discrete range type without canonical function

I want to create and use a discrete range type but I'm not willing to create a canonical function as there are about 30 years that I don't write a single line of C code, and it seems it is the only ...
0
votes
2answers
54 views

Declare variable for SELECT statement in Postgres function

I have modified my function, but I have problems with declaring variables. I use Postgres 8.4. CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) RETURNS TABLE ...
2
votes
2answers
69 views

Use array of composite type as function parameter and access it

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table. This is my type: ...
0
votes
0answers
29 views

How does the current_timestamp() or now() work? I am getting inconsistent results

current_timestamp() returns time ahead of current time and then returns current time upon subsequent request. Below are two requests i gave to the mysql server in commandline. I was not able to ...
1
vote
1answer
71 views

Array of template type in PL/pgSQL function using %TYPE

I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE). I want to update it so one of those arguments is ...
7
votes
0answers
121 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
0
votes
0answers
22 views

PL/SQL: Temporary Table insertion in User Defined Functions

I have made a Global Temporary Table (We'll call it TT) and I am trying to insert values from a permanent table (We'll call it PT) into TP. Right now I am trying to run this certain setup inside my ...
3
votes
1answer
62 views

How do I change table used in from clause of a select based on udf parameter?

I have two tables A and B (with exactly the same columns and overall structure) and I'm trying to create a TVF with a select inside it that chooses to use A or B based on a parameter passed to the ...
1
vote
0answers
19 views

Question on comparing two fields in Access

I'm not sure if the title is fitting as I am not 100% sure if what I am doing is correct but here goes. Here is my enrolment table, I have added a field called pass/fail as you can see which is ...
3
votes
2answers
78 views

Creating calculated column

I want to create a rating weight and I need do calculate AVG, then find COUNT and multiply both to get weight. I have something like that: SELECT post_id, COUNT(CASE WHEN vote > 0 THEN vote ELSE ...
1
vote
1answer
54 views

Does the function inside the Select clause evaluate the passed in columns before the where clause?

I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question. select ...
0
votes
0answers
31 views

Variable type to store result of SELECT returning multiple rows in PostgreSQL

I'm writing a function in PostgreSQL 9.3 and I want to store the names of the columns of a table into a variable, but I'm struggling to find the correct data type: CREATE OR REPLACE FUNCTION ...
0
votes
0answers
19 views

Use array operators inside postgresql function

I'm trying to create a postgresql. But I keep having an error ERROR: operator does not exist: integer[] <@ integer Hint: No operator matches the given name and argument type(s). You might ...
4
votes
1answer
718 views

Getting error “must declare the scalar variable” in SQL function even though it is declared

I am altering function like: ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY) RETURNS TypePreviousListingResult AS BEGIN DECLARE @tbl_ListingDateDetails ...
4
votes
1answer
175 views

PL/pgSQL issues when function used twice (caching problem ?)

I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem. I have this function: CREATE FUNCTION sp_connect(mail character varying, passwd character ...
0
votes
0answers
31 views

PostgreSQL - Are CREATE FUNCTION and DROP FUNCTION locking when used inside different transactions?

I'm having a locking issue on PostgreSQL 9.4.5. I run two or more transactions simultaneously (generally there are 2 running at the same time, but 2+ may happen sometimes) reading from the same tables ...
0
votes
1answer
63 views

Why do string functions on SQL based database servers start at position 1 instead of 0? [closed]

This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the ...
5
votes
1answer
189 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
0
votes
0answers
19 views

Is there an alternative to delete a procedure inside a procedure?

For my Bachelor Thesis I try to automate some recurring tasks in MySQL. One task consists of the deletion of a specific stored procedure. Normally I would create a stored procedure which takes one ...
4
votes
1answer
139 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
0
votes
1answer
54 views

select rows older then column DATETIME - INT value of other column in single query

I have 2 columns in my table: (DATETIME)lastsendattempt (INT)sendgap I know I can select all rows that have a "lastsendattempt" DATETIME which is older then the current time. But, am I able to use ...
0
votes
0answers
16 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the ...
1
vote
0answers
30 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
5
votes
4answers
188 views

Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition

I have a need for a computed column to resolve an implicit conversion problem. I have a column that is declared as a VARCHAR that should only ever store integers, but is occasionally populated in ...
0
votes
0answers
26 views

mysql: can not find a function being called

I am new to mysql and there is a function (or something similar) in existing functions in the db. While I compile using the function on my server if compiles fine, but does not compile in my local. It ...
2
votes
1answer
65 views

Create a function from another function

Is it possible to create a new function from another plpgsql function? Something like this: CREATE OR REPLACE FUNCTION func_test() RETURNS VOID AS $BODY$ BEGIN CREATE OR REPLACE FUNCTION ...
0
votes
0answers
40 views

MySQL performance affected by calling functions from inside procedures?

this is the very first question I post, so please bear with me ... When working on a project, I tried to get the LAST_INSERT_ID for a table, by invoking it from inside a procedure right after an ...
6
votes
2answers
351 views

User Defined Function (UDF) in master database

I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database ...
0
votes
1answer
25 views

Postgresql 9.4: How to find out the stack level of nested function

Here are the two cases: Function fA fetches some rows and returns a table Function fB calls function fA and returns a table Within fA, how could I know whether it's been called from the program or ...