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)

3
votes
3answers
25 views

CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE

I am facing the following error: ERROR: functions in index expression must be marked IMMUTABLE When trying to create index like this: CREATE INDEX full_phone_number ON orders_clientphone (...
1
vote
0answers
10 views

Shouldn't DISCARD PLANS re-evaluate every function definition in postgres?

Hi have a set of functions that take a row of a table (let's call it bookings) as input parameters. Some of these functions call other functions, and they are used to calculate a materialized view. ...
2
votes
1answer
45 views

Creating a helper UDF or stored procedure and limiting external access

I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'. I'm working on a security system and I want client ...
4
votes
1answer
41 views

Function that returns table with an additional column

I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)? For example, ...
0
votes
0answers
17 views

MySQL - sys_exec to trigger python scripts - Alternatives

I am trying to log some heartbeat data to check successful completion of errors in some frequently running stored procedures to Datadog. At the moment these are triggered from a Java application ...
0
votes
0answers
19 views

How to delete/disable Extended SQL functions or turn off extended stored procedures in postgres

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure how to implement: Extended SQL functions ...
0
votes
1answer
25 views

SQL Methods Function to add “Gold Medal” for anniversary

I'm trying to create a method function on SQL using Oracle Developer. The member function will give employees a gold medal after 12 years of service to the bank, a silver medal after 6 years of ...
4
votes
2answers
513 views

Why is the schema prefix (dbo) mandatory when we call a function?

When user is mapped with default schema (dbo) and we can select all tables under [dbo] without prefixing the schema. We can also execute stored procedures without prefix if it's under the default ...
0
votes
1answer
21 views

Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

I have a function which returns: TABLE (id TEXT, collected TIMESTAMPTZ) In another function, I would like to be able to pass the result of that first function as a parameter. For example: CREATE ...
0
votes
2answers
73 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
21 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
264 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
60 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
34 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
21 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
77 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 ...
34
votes
1answer
809 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
128 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
55 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
44 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
39 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
36 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
62 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
140 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
71 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
111 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
39 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
34 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
68 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
45 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
40 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
76 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
154 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
27 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
82 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
165 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
97 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
160 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
32 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
70 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
22 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
82 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
58 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
45 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 ...