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)

0
votes
2answers
72 views

Convert Function

I am working on converting a few dates to make them easier to read and when I call upon the convert function to do so, they simply don't convert. When I run the queries they look the same. The code is ...
0
votes
0answers
19 views

conditional trigger function for history table

I have a history table that tracks modifications to attributes/geometry for each layer I need to set a condition that if the attribute is 'Y' for the column "export" in the "span1" layer, the trigger ...
5
votes
1answer
256 views

Microsoft SQL Server creating an Inline function

Hey guys I'm completely new to SQL where I have to write a user-defined function (UDF) that calculates a student's GPA for a given time frame. The Inputs are StudentId int, ClassStartDateStart ...
1
vote
1answer
47 views

PostgreSQL: Pass table as argument in function

I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example: CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId ...
0
votes
1answer
31 views

Abstracting away filtering behavior from Oracle SQL Function

I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records. In an application ...
1
vote
0answers
17 views

Postgres - Trying to backup Functions only

I'm trying to follow Craig Ringer's answer to this question: http://stackoverflow.com/questions/13758003/how-to-take-backup-of-functions-only-in-postgres My command is: psql -U username -AT db_name -...
-3
votes
2answers
76 views

Case statement by using IsNull and IsNotNull [closed]

ALTER FUNCTION [dbo].[addC]() RETURNS varchar(255) AS BEGIN DECLARE @desc varchar(255); DECLARE @desc1 varchar(255); SET @desc = '' SET @desc1 = '' Declare c1 Cursor for ...
31
votes
1answer
762 views

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans: If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure ...
6
votes
3answers
105 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
6 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
50 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
38 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
35 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
80 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
34 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
20 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
48 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 , result].....
2
votes
3answers
116 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
62 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
107 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
37 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
67 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 in(...
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
1answer
36 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
75 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
151 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
24 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
69 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
113 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
83 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 ...
8
votes
0answers
146 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
27 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
66 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 TVF:...
1
vote
0answers
20 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
81 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
55 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
38 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
21 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
882 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
205 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
35 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
69 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
222 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
24 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
156 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
55 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 ...