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
87 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 ...
1
vote
1answer
30 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 ...
2
votes
1answer
183 views

Joining two inline functions, slows considerably when filtering on second function

I have two inline functions that run quickly enough alone. When I left join them like this: select * from Function1(-1) F1 left join Function2(-1) F2 on F1.key = F2.key ...I get my result set in ...
12
votes
1answer
3k views

How does this syntax work? {fn CurDate()} or {fn Now()} etc

Recently I've been looking through some fairly old stored procedures that were written for SQL Server 2005, and I've noticed something that I don't understand. It appears to be some type of function ...
0
votes
2answers
696 views

Using output value of column from function for another column in SQL*Loader

I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here. Please ...
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 ...
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 ...
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 ...
3
votes
2answers
734 views

Password generator function

What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions? I would like to create a ...
5
votes
2answers
26k views

Granting Execute Permissions on Stored Procedures, Functions and Views

I am using a user which has read permission only. It basically has permission to Connect Execute However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
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, ...
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 ...
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 ...
0
votes
1answer
97 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: ...
10
votes
1answer
14k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
2
votes
3answers
97 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 ...
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 ...
0
votes
2answers
34 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 , ...
1
vote
3answers
48 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
0answers
32 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. ...
3
votes
1answer
186 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
10
votes
3answers
144 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
31 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 ...
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 ...
-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 ...
2
votes
2answers
64 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: ...
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: ...
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
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 ...
1
vote
1answer
67 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 ...
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 ...
7
votes
0answers
119 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
955 views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
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 ...
2
votes
2answers
210 views

PostgreSQL - is it possible to use a plpgsql function in a bash script?

Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up). Problem : Since between the partial-backup ...
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
88 views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
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
704 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 ...
34
votes
2answers
25k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
4
votes
1answer
172 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 ...
7
votes
2answers
5k views

Why scalar valued functions need execute permission rather than select?

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select? meanwhile a table valued functions works just fine with only select permission or ...
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 ...
5
votes
1answer
187 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 ...